アクセスの正規化？

質問

前に質問したことがあったのですが、やってみてできなかったので再度質問させてもらいます。今AのテーブルとBのテーブルをクエリで結合していて、簡単に書くと下記のようなテーブルになっています。

【県名】　　【地方】　　【数量】
　群馬　　　　関東　　　　　10
　埼玉　　　　関東　　　　　15
　栃木　　　　関東　　　　　20
　東京　　　　関東　　　　　50
　新潟　　　　北陸　　　　　15
　富山　　　　北陸　　　　　15
　石川　　　　北陸　　　　　35
　鳥取　　　　中国　　　　　 8
　島根　　　　中国　　　　　12
　広島　　　　中国　　　　　40

これを、下記の様に変えたいんですが、どうすればいいでしょうか？
＜第1段階＞
【地方】　　【県名】　　【数量】
　関東　　　　群馬　　　　　90
　関東　　　　埼玉　　　　　90
　関東　　　　栃木　　　　　90
　関東　　　　東京　　　　　90
　北陸　　　　新潟　　　　　65
　北陸　　　　富山　　　　　65
　北陸　　　　石川　　　　　65
　中国　　　　鳥取　　　　　60
　中国　　　　島根　　　　　60
　中国　　　　広島　　　　　60

＜第2段階＞
【地方】　　 【県名】　　　　　　　 【数量】
　関東　　群馬・埼玉・栃木・東京　　　　　90
　北陸　　 新潟・富山・石川　　　　　 65
　中国　　 鳥取・島根・広島　　　 　　60

このように最終的には第2段階にしたいのですが、やり方がわかりません。どなたか、分かる方教えて下さい。ちなみに集計クリエの「グループ化」「合計」では、できませんでした。
お願いします。

回答No.1

DexMachina · ベストアンサー

すみません、こちらの補足を見て、サンプルを作っていたんですが、
回答が間に合いませんでした(汗)
http://oshiete1.goo.ne.jp/qa5162308.html

<第1段階>

通常のクエリ(選択クエリ)で、【数量】を演算フィールドにします。
具体的には、新規クエリのデザインビューで、【地方】・【県名】の
2フィールドをそれぞれドラッグ＆ドロップなどで表示対象に追加
した上で、その隣の、空白の『フィールド』欄に、以下のような式を
入力します：

　数量: DSum("[数量]", "テーブル名", "[地方]='" & [地方] & "'")

※「テーブル名」には、上記クエリの基にしたテーブルの名前を
　　指定します(前後のダブルクォート「"」は必要)。
　 また、2個目の「[地方]」の前後で1個ずつシングルクォートを
　 使っているので、見落とさないようご注意下さい。

<第2段階>
前述の質問で、サンプル作成に手間取ったのがこちらです(汗)
幾つか方法があるかと思いますが、ここでのやりとりでの伝えやすさ
から、ユーザー関数を作成して、第1段階と同様の手順を踏むことと
したいと思います。

【ユーザー関数の準備】
データベースウィンドウ(テーブルやフォームなどが表示される画面)で、
『モジュール』を選択して『新規作成』をクリックしたら、そこに自動で
入力された文字は全て削除した上で、以下のコードを貼り付けて
ください：

'～～～～～～～～～～ここから貼付～～～～～～～～～～
Option Compare Database
Option Explicit

Public Function DMembers(sExpr As String, sDomain As String, _
　　　Optional sCriteria As String, Optional sOrder As String) As String
On Error GoTo エラー処理

　 Dim Rsl As String, DBS As DAO.Database, DRS As DAO.Recordset, strRS As String, sData As String

　 'ベースとなるSQL文(クエリ)を用意
　 strRS = "Select * From " & sDomain

　 'Where条件を指定した場合の対応
　 If Len(sCriteria) Then
　　　'SQL文にWhere条件を追加
　　　strRS = strRS & vbCrLf & "Where " & sCriteria
　 End If

　 '並べ替えを指定した場合の対応
　 If Len(sOrder) Then
　　　'SQL文に並替条件を追加
　　　strRS = strRS & vbCrLf & "Order by " & sOrder
　　　'並替条件指定時に、列挙データ内に括弧付きでその値も追記するための準備
　　　sData = Trim(Replace(sOrder, " Desc", ""))
　 End If

　 'データを取得するためにレコードセットを開く
　 Set DBS = CurrentDb: Set DRS = DBS.OpenRecordset(strRS)
　 With DRS
　　　'対象フィールドのデータを、カンマ(+半角Space)区切りで列挙
　　　Do Until .EOF
　　　　 Rsl = Rsl & ", " & .Fields(sExpr)
　　　　 '並替条件指定時は、括弧付きでその値を追記
　　　　 If Len(sData) Then
　　　　　　Rsl = Rsl & "(" & .Fields(sData) & ")"
　　　　 End If
　　　　 '次のレコードに移動
　　　　 .MoveNext
　　　Loop
　　　.Close
　 End With

　 '先頭につく、余分なカンマと半角Spaceを削除
　 Rsl = Mid(Rsl, 3)

終了処理:
　 DMembers = Rsl: Set DRS = Nothing: Set DBS = Nothing: Exit Function
エラー処理:
　 Rsl = "#Error": MsgBox Err & "：" & Error$, , "DMembers(関数)": Resume 終了処理
End Function

'～～～～～～～～～～ここまで貼付～～～～～～～～～～

貼り付けたら、適当な名前をつけてモジュールを保存してください。

【クエリの作成】
こうして作成したDMembers関数を、第1段階でのDSum関数と同様に
演算フィールドで使用します。
但し、第1段階と違って、こちらは「集計クエリ」を使用します。
(【地方】と【県名】は「グループ化」、【数量】は「合計」を指定)

【地方】と【数量】はドラッグ＆ドロップなどで追加し、【県名】を演算
フィールドにするわけですが、「第2段階」として提示された形にする
場合は以下の式を使用します：

　県名: DMembers("[県名]","テーブル名","[地方]='" & [地方] & "'")

また、各県の数量を参考として括弧内に表示させることもできるように
しました。その機能を使う場合は、以下のようにします：
(なお、この場合は各県は数量の多い順に並びます)

　県名: DMembers("[県名]","テーブル名","[地方]='" & [地方] & "'","[数量] Desc")

※「"[数量] Desc"」は、数量の多い方から並べる(=数量で降順)
　　意味になります。
　数量の小さいほう法から並べる場合は、「"[数量]"」と指定します。

補足

DexMachinaさんのおかげで、やりたかったことができました！！！本当に本当にありがとうございました！！！！
それで、追記で質問になってしまうのですが、こういったモジュールはやはりかなり勉強しないと書けないのでしょうか？
それから、細かいことなのですが「＆」「′(シングルクォート)」「"(ダブルクォート)」の使い方の違いを教えていただけると嬉しいです。

回答No.3

DexMachina

No.1です。
・・・すみません、かなり長文になってしまいましたが・・・(汗)

> こういったモジュールはやはりかなり勉強しないと書けないのでしょうか？

私の場合、「必要になったときに、必要になった部分だけ調べる(時間的・
精神的に余裕があればその周辺の情報も少しかじる)」というやり方をして
きたので、なんとも言えないところがあります(汗)

例えば、今回のモジュールに関してで言えば、
　　「同じ【地方】内の全ての【県名】の列挙」
の部分が核になるわけですが、「Do～Loop」の部分はこういう場合の
定番操作といっていいと思います。

なので、これさえ知っていれば、今回の件に限らず
　　「何かの羅列が必要」　→　「Do Loop」を使おう
と条件反射的に方針が決まるわけです。
(実際には、「For～Next」や「For Each～Next」という似たようなことが
　できるものもあるので、それぞれの効率まで考えるとか、或いはもっと
　根本的に「ひとつのフィールドにまとめない方法があるのではないか」と
　いったことも考えますが)

上記の結果としてできるのが、前回のコードの以下の部分：

　strRS = "Select * From " & sDomain
　Set DBS = CurrentDb
　Set DRS = DBS.OpenRecordset(strRS)
　With
　　Do Until .EOF
　　　　Rsl = Rsl & ", " & .Fields(sExpr)
　　　　.MoveNext
　　Loop
　　.Close
　End With

実際には関数名とか変数の宣言(「Public Function ～」や「Dim ～」
の行)なども必要ですが、究極的にはこれだけで「県名の羅列」という
目的は達成できてしまいます。
(上記だとsCriteriaを入れていないので、地方に無関係の列挙ですが)

それ以外の部分は、実際に上ので動かしてみて、「どうせなら合計前の
数値もわかった方がいいかも」「だったらその順で並べ替えた方が」といった
思いつき(いってみれば余計なもの)を組み込んでいった、というだけです。

・・・ということで、それほど構えずに、まずはAccess付属のサンプルデータ
ベース(標準インストールでは省略されていたかも(汗))やWeb上の各種
コードから、見よう見まねで触ってみるのがよいかと思います。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

> 「＆」「′(シングルクォート)」「"(ダブルクォート)」の使い方の違い

「"」も「'」も基本的には「囲んだ文字列を、文字列として渡す・表示する」
のに使用します。
(但し、Visual Basic Editor(VBE：VBAでコードを記述する画面)では、
　「'」は、それ単独で「それより右側の部分をコード本文ではなく文字列
　(コメント)として扱う」という意味になります)

で、前回回答したDSum関数の引数の場合ですが・・・
【地方】フィールドが「関東」という値であることを意味する式は、
　　[地方]="関東"
　　(または「[地方]='関東'」/「地方="関東"」/「地方='関東'」でも可)
になります。
(「"」「'」で括らないと、「関東」は値ではなくフィールド等と解釈されます。
　なお、角括弧([ ])による括りは、それがフィールド・コントロール等で
　あることの明示手段になります：特にSpaceやピリオドなどの特殊な
　意味を持つ文字を含む名前の場合は必須)

ただ、そのまま「DSum("[数量]","テーブル名",[地方]="関東")」と
入れると、今度は「地方」がフォーム上のコントロール等と解釈されて
しまいます。
そのため、上記の引数全体を、さらに「"」や「'」で括ってやる必要が生じます。

ここで、「[地方]="関東"」というように既に「"」を使用していて、さらに
「"」で括った(「"[地方]="関東""」)とします。
すると、Accessは「"[地方]="」まででワンセットとして判断してしまうので、
結果としてエラーになります。

これを回避するため、
　　DSum("[数量]","テーブル名","[地方]='関東'")
というように、「"」と「'」を併用する必要がある、ということです。

・・・さて、上記の式をそのままクエリに組み込んでしまうと、引数には
「関東」が指定されているので、地方が関東でも北陸でも計算結果には
関東の値が表示されてしまいます。

そこで、「='関東'」の「関東」の部分を、『「"」の括り』の外に出して、
「文字列(の一部)」から「フィールド」として解釈されるようにしたのが、
前回回答の式になります：
　　DSum("[数量]","テーブル名","[地方]='" & [地方] & "'")

「"」の括りの外に出た2番目の「[地方]」は、クエリの各レコードの「地方」の
値を参照することになります。
また、「&」は、第3引数の「"[地方]='"」と「[地方]」(→レコードによって
「関東」等に変化)と「"'"」の3つのパーツを連結して、ひとつの文字列に
するためのものです。
これにより、各レコード上で、例えば地方が「関東」のものでは、テキスト
連結の結果、「地方='関東'」という文字列に読み替えられることになります。

・・・以上、長くなりましたが、参考まで。

お礼

毎回私の意味不明は質問に答えていただきありがとうございました。
私も、少しずつ勉強していきたいと思います。

回答No.2

shinkami

回答になっていないかもしれませんがこのデータを一つのテーブルで処理するより次のように二つのテーブルで処理します。
・TBL地方(地方ID,地方名)…地方IDを主キーに
・TBL県名(県名ID,地方ID,数量)…県名IDを主キーに
この二つのテーブル間に地方IDを通してリレーションシップ(関連付け)を設定します。
既にデータがあるテーブルを正規化するには[ツール]→[解析]→[テーブルを正規化する]の仕掛けがよういされていますが
ACCESS200ではうまく機能しません。

そのため次のように正規化します。
既に存在するテーブルを右クリックから[名前を付けて保存する]→「TBL地方」
・出来上がったTBL地方のフィールドを地方名のみを残して、重複しているレコードを削除した後地方を主キーに設定する
・もとのテーブルの地方に「テーブルルックアップ」(図参照）を設定します。
・TBL地方(地方)…地方を主キーに
・TBL県名(県名ID,地方,数量)…県名IDを主キーに、地方にテーブルルックアップ

お礼

アドバイスありがとうございました。