MS accessのクエリの作り方について

質問

お世話になります。

画像のようなテーブル1があり、
クエリ結果(1)、クエリ結果(2)を出力しようとしています。
どのようにクエリを作ればよいか教えていただけないでしょうか。

access2000を使用しております。
VB・SQLの知識がないため、クエリで作ろうとしています。

よろしくお願いします。

回答No.4

30246kiku · ベストアンサー

＃３です

> 　　必要であれば、補足ください（準備済）

でしたが、せっかく用意していたものが日の目を見ないのもなんだかなぁ・・・ということで、
迷惑かもしれませんが、以下に続編を・・・

「▲▲」生成時のルールを設定すれば・・・
・前述した結果の様に、仮定した最終的なもので作る・・・
・いや、階層はやっぱり必要なので、辿った順に「数」を計算しつつ作成する

以下は後者の一例です
テーブル「▲▲」のフィールドは「親分」「親」「子」「数」「階層」の５つ
（事前に作っておきます：
　主キーにできそうなものはないので、オートナンバのフィールドを追加しておいても）

テーブル「★★」の内容
親　子　数
a　　b　　1
b　　c　　1
c　　d　　1
c　　d'　　1
d　　e　　2
d'　　e'　　2
b　　d　　1
aa　　bb　　1

↓

出来上がるテーブル「▲▲」の内容
親分　親　子　数　階層
a　　a　　b　　1　　1
a　　b　　c　　1　　2
a　　c　　d　　1　　3
a　　d　　e　　2　　4
a　　c　　d'　　1　　3
a　　d'　　e'　　2　　4
a　　b　　d　　1　　2
a　　d　　e　　2　　3
aa　　aa　　bb　　1　　1

※ 親="d" 子="e" のレコードは２つありますね
　辿るルートによって、階層が違ってますね

では、「▲▲」を参照する時に

SELECT 親分, 子, Sum(▲▲.数) AS 数
FROM ▲▲
GROUP BY 親分, 子;

とすると

親分　子　数
a　　b　　1
a　　c　　1
a　　d　　2
a　　d'　　1
a　　e　　4
a　　e'　　2
aa　　bb　　1

となって、仮定した最終的なものが得られることになります
後は、何でグループ化するかですね

SELECT 親, 子, Sum(▲▲.数) AS 数, Min(▲▲.階層) AS 階層
FROM ▲▲
WHERE 親分='a'
GROUP BY 親, 子;

とすれば、結果は以下の様になります

親　子　数　階層
a　　b　　1　　1
b　　c　　1　　2
b　　d　　1　　2
c　　d　　1　　3
c　　d'　　1　　3
d　　e　　4　　3
d'　　e'　　2　　4

チョッと横道）
以下のクエリを作成して、表示を確認してみてください

TRANSFORM Sum(数) AS 値
SELECT 親
FROM ▲▲
GROUP BY 親
PIVOT 子;

とか

TRANSFORM Sum(数) AS 値
SELECT 親分
FROM ▲▲
GROUP BY 親分
PIVOT 子;

標準モジュールに以下を記述します

関数 fncMkTbl の実行で「▲▲」に全部展開します
また、関数 fncMkTblOne の実行では、引数に指定した「親」だけを作り直します

Private Sub ReMk(rsP As ADODB.Recordset, iNst As Long, iFct As Long _
　　　　　　　　　　　　, sFix As String, sSrc As String)
　　Dim rs As ADODB.Recordset
　　Dim sSql As String
　　Const CSQL As String = "INSERT INTO ▲▲(親分,親,子,数,階層) VALUES " _
　　　　　　　　　　　　& "('{%1}','{%2}','{%3}',{%4},{%5});"
　　Set rs = rsP.Clone
　　rs.Filter = "親='" & Replace(sSrc, "'", "''") & "'"
　　Do While (Not rs.EOF)
　　　　sSql = CSQL
　　　　sSql = Replace(sSql, "{%1}", Replace(sFix, "'", "''"))
　　　　sSql = Replace(sSql, "{%2}", Replace(rs("親"), "'", "''"))
　　　　sSql = Replace(sSql, "{%3}", Replace(rs("子"), "'", "''"))
　　　　sSql = Replace(sSql, "{%4}", iFct * rs("数"))
　　　　sSql = Replace(sSql, "{%5}", iNst)
　　　　CurrentProject.Connection.Execute sSql
　　　　Call ReMk(rs, iNst + 1, iFct * rs("数"), sFix, rs("子"))
　　　　rs.MoveNext
　　Loop
　　rs.Close
　　Set rs = Nothing
End Sub

Public Sub fncMkTbl()
　　Dim rsP As New ADODB.Recordset
　　Dim rs As New ADODB.Recordset

　　CurrentProject.Connection.Execute "DELETE * FROM ▲▲;"
　　rs.Source = "SELECT * FROM ★★ ORDER BY 親, 子;"
　　rs.Open , CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　rsP.Source = "SELECT DISTINCT 親 FROM ★★ WHERE 親 NOT IN (SELECT 子 FROM ★★);"
　　rsP.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　While (Not rsP.EOF)
　　　　Call ReMk(rs, 1, 1, rsP("親"), rsP("親"))
　　　　rsP.MoveNext
　　Wend
　　rsP.Close
　　rs.Close
End Sub

Public Sub fncMkTblOne(sFix As String)
　　Dim sSql As String
　　Dim rs As New ADODB.Recordset
　　Const CSQL As String = "DELETE * FROM ▲▲ WHERE 親分='{%1}';"

　　sSql = Replace(CSQL, "{%1}", Replace(sFix, "'", "''"))
　　CurrentProject.Connection.Execute sSql
　　rs.Source = "SELECT * FROM ★★ ORDER BY 親, 子;"
　　rs.Open , CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　Call ReMk(rs, 1, 1, sFix, sFix)
　　rs.Close
End Sub

※※ 最大階層が 5 だった場合

以下のクエリ「Q★★」を作っておきます

SELECT Q1.親, Q1.子 AS F1, Q2.子 AS F2, Q3.子 AS F3, Q4.子 AS F4, Q5.子 AS F5 FROM
(((((SELECT DISTINCT 親 FROM ★★ WHERE 親 NOT IN (SELECT 子 FROM ★★)) AS Q0
LEFT JOIN ★★ AS Q1 ON Q0.親=Q1.親)
LEFT JOIN ★★ AS Q2 ON Q1.子=Q2.親)
LEFT JOIN ★★ AS Q3 ON Q2.子=Q3.親)
LEFT JOIN ★★ AS Q4 ON Q3.子=Q4.親)
LEFT JOIN ★★ AS Q5 ON Q4.子=Q5.親;

このクエリを元に、以下クエリを作ります
（一度上記のクエリ表示を確認してみてください）

SELECT 親, 子, fncSearchSum(親,子) AS 数 FROM (
SELECT 親, F1 AS 子 FROM Q★★ WHERE F1 Is Not Null
UNION
SELECT 親, F2 FROM Q★★ WHERE F2 Is Not Null
UNION
SELECT 親, F3 FROM Q★★ WHERE F3 Is Not Null
UNION
SELECT 親, F4 FROM Q★★ WHERE F4 Is Not Null
UNION
SELECT 親, F5 FROM Q★★ WHERE F5 Is Not Null
) AS Q1;

結果は以下の様になります

親　子　数
a　　b　　1
a　　c　　1
a　　d　　2
a　　d'　　1
a　　e　　4
a　　e'　　2
aa　　bb　　1

※※ どの方法なら使いものになるか・・・いろいろやってみて
テーブル「▲▲」に展開しておいた方が良さそうな気はします

※ テーブル「▲▲」に展開する時には、時間はかかっても良いんですよね？？

※※ なお、途中の数量を変更してみて、最終的な「数」はどうなるか・・・
　確認は必須ですよね

お礼

補足説明までして頂きありがとうございます。
ただ、私の力量不足で回答No.3の内容がまだ確認できていない状況です。

せっかく回答を頂いているところ申し訳ありません。
上記の補足していただいた説明も加え、しっかり確認させていただきます。

補足

30246kiku様

大変長い間回答できずに申し訳ありませんでした。
別件で手をつけられず、いつまでもこのままにしておくのも失礼になりますので、ベストアンサーとさせて頂きます。
ありがとうございました。

回答No.3

30246kiku

＃２です

>「aを作るためにはbが1ヶ、bを作るためにはcが1ヶ・・・」
> といった内容のテーブルから、
> クエリ結果となるように出力するにはどうすればよいかを質問させていただきました。

というものが「何か」ですよね
であれば、

> まさに求めていた結果となりました。

にはならないと思います（たまたま似た結果になっている）

> ※ 階層は、得ていた Recordset を使って、初めに見つかったものを返します

この部分は、気休め程度です。
例えば、 "a" → "d" の階層を求める時、
・"a" → "b" → "d"
・"a" → "b" → "c" → "d"
のルートがあった場合、どちらか１つ・・・

Recordset を得る時に、"SELECT * FROM ★★ ORDER BY 親, 子;"　で得ておけば
・"a" → "b" → "c" → "d"
の方が初めに見つかると思います
でも、これは、"a" "b" "c" "d" という文字列だったからにすぎません
違う文字列パターンであれば、違う方が選ばれるかも・・・
なので、階層の小さい方を・・・ とか・・・決めれません
ただ、ORDER BY を指定する事によって、初めに見つかるルートは固定できるかと・・・
階層の小さい方にしたい・・・　とかになると、
一度見つかっても全ルートを探し回った後で判別しなくてはなりません

「何か」が提示された事で、それ用に考えてみると
「階層」は不要なのでは？　「数」の方が重要なのでは？

以下は、クエリ結果１に特化します。
（クエリ結果２はどの様な場面で必要なのか、チョッとわからないので）
（後述のテーブル「★★」「▲▲」から「子」に条件付ければ求められるのでは？）

「親」を作る時には、
・どんな「子」が必要で
・その時に必要な総数がわかる

細かいルール（仕様）はわからないので、以下の様なデータがあったとします
（提示されたデータの最後に b　d　1　を追加しています）

親　子　数
a　　b　　1
b　　c　　1
c　　d　　1
c　　d'　　1
d　　e　　2
d'　　e'　　2
b　　d　　1

結果として、以下を得たいものと仮定します
（親="a" 子="a" は、いらないでしょう・・・）

親　子　数
a　　b　　1
a　　c　　1
a　　d　　2
a　　d'　　1
a　　e　　4
a　　e'　　2

d を構成する e は 2 つ必要だけど、d は c , b で各 1 つ必要なので
結果 e は 4 つ必要・・・

> これではなくて、階層が >0 の場合、各「数」を掛け合わせていくのか・・・・
といっていたのは、この事ですね

標準モジュールに以下を記述します

Private Function ReSum(rsP As ADODB.Recordset, iFct As Long _
　　　　　　　　　　　　, sSrc As String, sDest As String) As Long
　　Dim rs As ADODB.Recordset

　　ReSum = 0
　　Set rs = rsP.Clone
　　rs.Filter = "親='" & Replace(sSrc, "'", "''") & "'"
　　Do While (Not rs.EOF)
　　　　If (rs("子") = sDest) Then
　　　　　　ReSum = ReSum + iFct * rs("数")
　　　　Else
　　　　　　ReSum = ReSum + ReSum(rs, iFct * rs("数"), rs("子"), sDest)
　　　　End If
　　　　rs.MoveNext
　　Loop
　　rs.Close
　　Set rs = Nothing
End Function

Public Function fncSearchSum(sSrc As String, sDest As String) As Long
　　Dim rs As New ADODB.Recordset

　　rs.Open "★★", CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　fncSearchSum = ReSum(rs, 1, sSrc, sDest)
　　rs.Close
End Function

クエリのSQLビューで以下を記述します

SELECT 親, 子, fncSearchSum(親,子) AS 数 FROM
(SELECT DISTINCT 親 FROM ★★ WHERE 親 NOT IN (SELECT 子 FROM ★★)) AS Q1,
(SELECT DISTINCT 子 FROM ★★) AS Q2
ORDER BY 親, 子;

このクエリの表示が前述した結果になると思います。

※ このクエリはサンプル上では有効ですが、＃２で提示した様に
　親="aa"　子="bb"　数=1 をさらに追加した時の表示は以下の様になります

親　子　数
a　　b　　1
a　　bb　　0
a　　c　　1
a　　d　　2
a　　d'　　1
a　　e　　4
a　　e'　　2
aa　　b　　0
aa　　bb　　1
aa　　c　　0
aa　　d　　0
aa　　d'　　0
aa　　e　　0
aa　　e'　　0

※※　サンプル上ではソコソコ動いてますが、
対象のデータ量が多くなれば、それなりに遅くなっていくと思います。
遅くなる≒クエリで処理するレベルではなくなる？
このテーブル「★★」は頻繁に更新されるものとは思えない（？）ので
テーブル「★★」を元に、テーブル「▲▲」に展開しておいて、
用途によって「★★」or「▲▲」を参照する方法もあるかと・・・

「▲▲」を作成する時には、「親」→「子」を辿るだけにできるので
以下の無駄な「親」→「子」処理を排除できると思います

親　子　数
a　　bb　　0
aa　　b　　0
aa　　c　　0
aa　　d　　0
aa　　d'　　0
aa　　e　　0
aa　　e'　　0

※ 最大階層がわかっていれば、他の方法がありますが、現状では、
・ 親="a" の「子」達が何個になるのか辿ってみないとわからないので
　　レコード数を事前に設定する・・・できないような
・ クエリ上では、上記の無駄な「親」→「子」排除はできないような・・・
　（結果から、数=0 を除外すれば・・・　でも、0 を求める処理は走る）

※※ 「★★」→「▲▲」展開時、全部、親="a" だけとか・・・関数を設けても良いかも

※ そろそろ文字数制限に近づいてきているので、
　　展開する例が必要なのかわからないので、一旦ここまでとします。
　（最大階層がわかっている時の例も必要なのかわからないので）

　　必要であれば、補足ください（準備済）

お礼

お礼が遅くなりました。

回答ありがとうございます。
頂いた説明をしっかり確認しながら試させていただきます。

回答No.2

30246kiku

> クエリ結果は導き出したいイメージです。
> どのようにしたら導き出せるのかを質問させてもらっています。

これはどう解釈すれば良いのでしょうか
例えば、変数が２つあって、「何か」した後 10 になる
この「何か」を考えなさいという事になりますか？
イメージには「何か」というものはないのでしょうか？

どの様なルールで、どうしたいのか、わからないところが多々ありますが

クエリ結果１を出す方法（階層部分）だけになります
「数」は何を元にするのか・・・　わからないので「数」は考えない事にします

テーブル名を「★★」として記述していきます
以下を標準モジュールに記述しておきます

Private Function ReClass(rsP As ADODB.Recordset, iNst As Long _
　　　　　　　　　　　　, sSrc As String, sDest As String) As Long
　　Dim rs As ADODB.Recordset

　　ReClass = 0
　　Set rs = rsP.Clone
　　rs.Filter = "親='" & Replace(sSrc, "'", "''") & "'"
　　Do While (Not rs.EOF)
　　　　If (rs("子") = sDest) Then
　　　　　　ReClass = iNst + 1
　　　　Else
　　　　　　ReClass = ReClass(rs, iNst + 1, rs("子"), sDest)
　　　　End If
　　　　If (ReClass <> 0) Then Exit Do
　　　　rs.MoveNext
　　Loop
　　rs.Close
　　Set rs = Nothing
End Function

Public Function fncSearchClass(sSrc As String, sDest As String) As Long
　　Dim rs As New ADODB.Recordset

　　rs.Open "★★", CurrentProject.Connection, adOpenStatic, adLockReadOnly
　　fncSearchClass = ReClass(rs, 0, sSrc, sDest)
　　rs.Close
End Function

※ 循環するデータ構成になっていたら無限ループになります
　（対処方法はありますが・・・その処理が必要なのかわからないので省略）

循環例）

親　子
a　　b
b　　a

※ 階層は、得ていた Recordset を使って、初めに見つかったものを返します

例）"a" → "d" の階層を求める時

親　子
a　　b
b　　c
b　　d
c　　d

だった場合と

親　子
a　　b
b　　d
b　　c
c　　d

では、結果は違くなると思います
・rs.Open 部分を変更する
・Do While (Not rs.EOF) 部分で、一度なめて、なかったら先頭に戻って
等、対処してください

※ エラー処理は入れてません

クエリのSQLビューで以下を記述します

SELECT 親, 子, fncSearchClass(親,子) AS 階層 FROM
(SELECT DISTINCT 親 FROM ★★ WHERE 親 NOT IN (SELECT 子 FROM ★★)) AS Q1,
(SELECT 子 FROM ★★
UNION
SELECT 親 FROM ★★) AS Q2
ORDER BY 親, 子;

表示結果は以下の様になります

親　子　　階層
a　　a　　0
a　　b　　1
a　　c　　2
a　　d　　3
a　　d'　　3
a　　e　　4
a　　e'　　4

※ 提示されたサンプル上でのもの
「親」「子」を作成するルールがこれで良いのか、わからない
仮に、元データに 「親」aa　「子」bb が追加されていたとすると以下の様になります

親　子　　階層
a　　a　　0
a　　aa　　0
a　　b　　1
a　　bb　　0
a　　c　　2
a　　d　　3
a　　d'　　3
a　　e　　4
a　　e'　　4
aa　　a　　0
aa　　aa　　0
aa　　b　　0
aa　　bb　　1
aa　　c　　0
aa　　d　　0
aa　　d'　　0
aa　　e　　0
aa　　e'　　0

なお、

SELECT 親, 子, Nz(Sum(Q2.数),1) AS 数, fncSearchClass(親,子) AS 階層 FROM
(SELECT DISTINCT 親 FROM ★★ WHERE 親 NOT IN (SELECT 子 FROM ★★)) AS Q1,
(SELECT 子, 数 FROM ★★
UNION
SELECT 親, Null FROM ★★) AS Q2
GROUP BY 親, 子;

とでもすれば「数」も求めたい結果となりますが、正しい処理なのかは、わからない
これではなくて、階層が >0 の場合、各「数」を掛け合わせていくのか・・・・

※ クエリ結果２の方でも「階層」を求めるのは上記で対応できると思います
なお、クエリ結果２の「親」「子」を作成するルールが全くわからない
"a" ～ "e'" は、例題用の文字列なんですよね？

提示されたサンプルで、いろいろとコジツケれば、

SELECT 親, 子, IIF(親=子,1,Q5.数) AS 数, fncSearchClass(親,子) AS 階層 FROM (
SELECT Q1.親, Q2.子, Q2.数 FROM
(SELECT DISTINCT 親 FROM ★★ WHERE 親 IN ('a','b','c')) AS Q1,
(SELECT DISTINCT 子, 数 FROM ★★ WHERE 子 Like 'e*') AS Q2
UNION
SELECT Q3.子, Q4.子, Q4.数 FROM
(SELECT DISTINCT 子 FROM ★★ WHERE 子 Like '[de]*') AS Q3,
(SELECT DISTINCT 子, 数 FROM ★★ WHERE 子 Like 'e*') AS Q4
WHERE CBool(InStr(Q3.子,"'")) = CBool(InStr(Q4.子,"'"))
) AS Q5
ORDER BY 子, 親;

で同じ表はできますけど
ルールがハッキリしたら、上記クエリは捨ててください

※※ 処理性能はわかりません

以上

お礼

大変詳しい説明ありがとうございます。
さっそく試させていただきます。

テーブルにある「親」「子」のアルファベットは変数ではなく、
ただの名称として使いました。

一般的にBOMと呼ばれる部品（構成）表になります。
「aを作るためにはbが1ヶ、bを作るためにはcが1ヶ・・・」
といった内容のテーブルから、
クエリ結果となるように出力するにはどうすればよいかを質問させていただきました。

補足

試させていただきました。
まさに求めていた結果となりました。
ありがとうございます。

ただ、

------------------------------------------------------------------
※ 階層は、得ていた Recordset を使って、初めに見つかったものを返します

～～～

・rs.Open 部分を変更する
・Do While (Not rs.EOF) 部分で、一度なめて、なかったら先頭に戻って
等、対処してください
------------------------------------------------------------------

にありました通り、レコード順で結果が異なりました。
どちらのレコード順でも上の場合と同じ結果とするにはどのようにすればよかったでしょうか。

------------------------------------------------------------------
・rs.Open 部分を変更する
・Do While (Not rs.EOF) 部分で、一度なめて、なかったら先頭に戻って
等、対処してください
------------------------------------------------------------------

と、ヒントを頂いているにも関わらず、申し訳ありませんが、
教えていただけないでしょうか。

回答No.1

Granpa-pc

あなたの示しているクエーリー結果がテーブル1からどのようにして導き出されるのか理解できません。
もっと詳しく説明しなければ、誰も回答できないと思いますよ。

言葉で説明してください。

お礼

回答ありがとうございます。

クエリ結果は導き出したいイメージです。
どのようにしたら導き出せるのかを質問させてもらっています。
引き続き考慮願えないでしょうか。