みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

お世話になります。

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

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

よろしくお願いします。

投稿日時 - 2014-01-14 18:34:21

QNo.8429698

すぐに回答ほしいです

質問者が選んだベストアンサー

#3です

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

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



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

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

テーブル「★★」の内容
親 子 数
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" のレコードは2つありますね
 辿るルートによって、階層が違ってますね

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

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


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

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


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

投稿日時 - 2014-01-21 19:41:20

補足

30246kiku様

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

投稿日時 - 2014-02-11 20:21:01

お礼

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

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

投稿日時 - 2014-01-23 12:54:54

このQ&Aは役に立ちましたか?

4人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

#2です

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

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

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

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


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

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

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


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

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

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

細かいルール(仕様)はわからないので、以下の様なデータがあったとします
(提示されたデータの最後に 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 親, 子;

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

※ このクエリはサンプル上では有効ですが、#2で提示した様に
 親="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" だけとか・・・関数を設けても良いかも

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

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

投稿日時 - 2014-01-17 20:07:01

お礼

お礼が遅くなりました。

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

投稿日時 - 2014-01-20 07:54:58

ANo.2

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

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


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


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

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

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 の場合、各「数」を掛け合わせていくのか・・・・


※ クエリ結果2の方でも「階層」を求めるのは上記で対応できると思います
なお、クエリ結果2の「親」「子」を作成するルールが全くわからない
"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 子, 親;

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


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

以上

投稿日時 - 2014-01-15 19:33:11

補足

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

ただ、

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

~~~

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

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

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

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

投稿日時 - 2014-01-16 17:50:54

お礼

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

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

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

投稿日時 - 2014-01-16 12:35:49

ANo.1

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

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

投稿日時 - 2014-01-14 23:12:57

お礼

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

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

投稿日時 - 2014-01-15 08:11:23

あなたにオススメの質問