ACCESSについて教えてください。

質問

いつも大変お世話になっております。
すみませんが、今回も力をお貸しください。よろしくお願いいたします。
社内部品構成管理システムには下表のように部品構成表として登録して管理してあります。(レベル1まで)
AはB,G,Xという部品で構成されているとします。
BはA,R,Xという部品で構成されているとします。
親　　子
A 　　C
A 　　G
A　　 X
B 　　A
B 　　R
B 　　X
Bという部品の構成を全部見たいと思い、以下のようにACCESS等(部品構成表DB)で紐付けします。
親　　
子　―　親
　　　　　子　―　親
　　　　　　　　　　子
結果、BはAが含まれるので、以下のような結果が出力されます。
(現状、どこまで子があるか分からないので適当に部品構成表のDBテーブルを結んでいます。)
B A C
B A G
B A X
B R
B X
これを、(サマリー型というのでしょうか)
レベル1　B
レベル2　A
レベル3　C
レベル3　G
レベル3　X
レベル1　R
レベル1　X
のように出力させるにはどのようにやればいいのでしょうか？
VBAやSQLで出力可能でしょうか？
長くなりましたが、よろしくお願いいたします。

回答No.7

Dxak · ベストアンサー

＃１です
えっと・・・？

> Bの部品を使用する"H"という部品が親に追加されたとして、同じレベルに
> 同じ子品番が存在する場合もあります

ならば、＃５でお答えしたクエリで実行可能（作成可能）です

で、とりあえずなのですが・・・ＤＡＯを使用しＶＢＡでテーブル作成するルーチンを作成してみました（こっちだと、階層の深度が多になっても大丈夫だと思いますが、ＶＢＡがある程度判らないと辛いかな？、後、普通はＡＤＯなんだろうけど・・・そっちは、勉強してないのと、ＤＡＯを覚えているだけで、現在要としては足りるんで＾＾；）

まず、ＶＢＡの画面で「ツール」－「参照設定」で、「Microsoft DAO X.X Object Library」にチェックが入っているか確認してください

で、モジュールを新規作成で、

Option Compare Database
Option Explicit
Dim DAOrsOut As DAO.Recordset

Sub usTreeMade(RefTbl As String, PerFld As String, ChrFld As String, StrChr As String)

If IsNull(DLookup("Name", "MSysObjects", "Name = 'Tbl_Tree'")) Then
CurrentDb.Execute "Create Table Tbl_Tree ([Level] Long, [Parts] Char(50), [Tree_String] Char(50));"
Else
CurrentDb.Execute "DELETE * FROM Tbl_Tree;"
End If
' 木構造作成
Set DAOrsOut = CurrentDb.OpenRecordset("Tbl_Tree")
Call usTreeSub(RefTbl, PerFld, ChrFld, StrChr, StrChr, 1)
DAOrsOut.Close
Set DAOrsOut = Nothing

End Sub

Sub usTreeSub(RefTbl As String, PerFld As String, ChrFld As String, StrChr As String, usTree As String, usLevel As Integer)

Dim DAOrsRef As DAO.Recordset
Dim NextStr As String
Dim mySQL As String

' 親の登録処理
With DAOrsOut
.AddNew
.Fields("Level") = usLevel
.Fields("Parts") = RTrim$(StrChr)
.Fields("Tree_String") = RTrim$(usTree)
.Update
End With
' 子の検索
mySQL = "Select * From " & RefTbl & " Where " & PerFld & " = '" & StrChr & "' ;"
Set DAOrsRef = CurrentDb.OpenRecordset(mySQL)
With DAOrsRef
While Not .EOF
NextStr = .Fields(ChrFld).Value
' 子の登録処理（再帰）
If InStr(usTree, NextStr) = 0 Then
Call usTreeSub(RefTbl, PerFld, ChrFld, NextStr, usTree & " - " & NextStr, usLevel + 1)
End If
.MoveNext
Wend
.Close
End With
Set DAOrsRef = Nothing

End Sub

をコピー＆ペーストして、フォーム上のコマンドボタンで・・・

call usTreeMade("部品構成表DB", "親", "子", "H")

で、呼び出せば・・・テーブルの「Tbl_Tree」と言うものに結果出力します
"H"の部分をフォーム上のコントロールで置き換えれば、他にも使えたり、汎用性が増えるかな？

お礼

有難うございます。
仕事上、まだ教えてもらった事さえ実行まで追いついていない現状と勉強しないと追いつけてない現状もあるので、教えてもらったことを参考に勉強していきたいと思います。(DAOについて説明、有難うございます)
最後まで付き合ってくださって有難うございました。<(_ _)>

回答No.6

noname#140971

一介のアパレルのデザイナです。
ですから、無茶ややこしい SQL文なんて考える能力はありません。
ですから、私なら、目的に沿ったテーブル構造を採用するのみです。

＜部品一覧＞P=親、C=子

ID____区分____部品名
1_____P_________A
2_____P_________B
3_____C_________A
4_____C_________C
5_____C_________G
6_____C_________X

このような＜部品一覧＞を用意します。
＜部品構成表＞には、それぞれ[ID]でリンクさせて管理します。
この場合、[孫部品名]の登録は、順序さえ間違えなければ自動登録できます。
[子部品名]を入力した段階で＜子-孫＞に対応する＜親-子＞の有無を検索するだけ。

＜部品構成表＞

ID____親部品名__子部品名__孫部品名
1_____A_____________C
2_____A_____________G
3_____A_____________X
4_____B_____________A_____________C
5_____B_____________A_____________G
6_____B_____________A_____________X
7_____B_____________R
8_____B_____________X

お礼

アドバイス、有難うございます。

回答No.5

Dxak

＃１です
度々、補足要求で申し訳ありません m(_ _)m
単純に、書いてある例を元に考えれば・・・

SELECT "レベル1" AS レベル, とりあえず部品表.部品_1st AS 部品名, とりあえず部品表.部品_1st As Sort_Name
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_1st
HAVING (((とりあえず部品表.部品_1st) Is Not Null))
union SELECT "レベル2" AS レベル, とりあえず部品表.部品_2nd AS 部品名, とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd As Sort_Name
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_2nd,とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd
HAVING (((とりあえず部品表.部品_2nd) Is Not Null))
union SELECT "レベル3" AS レベル, とりあえず部品表.部品_3rd AS 部品名, とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd & とりあえず部品表.部品_3rd As Sort_Name
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_3rd,とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd & とりあえず部品表.部品_3rd
HAVING (((とりあえず部品表.部品_3rd) Is Not Null))
UNION SELECT "レベル4" AS レベル, とりあえず部品表.部品_4th AS 部品名, とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd & とりあえず部品表.部品_3rd & とりあえず部品表.部品_4th As Sort_Name
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_4th,とりあえず部品表.部品_1st & とりあえず部品表.部品_2nd & とりあえず部品表.部品_3rd & とりあえず部品表.部品_4th
HAVING (((とりあえず部品表.部品_4th) Is Not Null))
ORDER BY Sort_Name;

で、ソートを加えるだけで、単純に出来上がりなのですが、色々、他に条件があるのでは無いかと疑ってるのですが・・・

例えば、
> 親　　子
> A 　　C
> A 　　G
> A　　 X
> B 　　A
B 　　A
> B 　　R
> B 　　X

と、Ｂ下にＡが２個発生する可能性があるのではないかとか、いろいろ考えると切が無いんだけど、わかる範疇で条件の追加してもらえますか？
なんとなく、考えてるとＶＢＡでＤＡＯ操作するなりテーブルへ書き込んで実施した方が早いような気がしてたりするんですが・・・

補足

すみません、こちらこそ度々考えてくださって感謝しております。
（実際、Dxakさんには、毎回助けられて、文では表せない程、感謝しております。）
>Ｂ下にＡが２個発生する可能性があるのではないか
ですが、1つの親(品番)に対して1つの子(品番)です。(常に1品番―1品番で部品構成表に登録状態）
>わかる範疇で条件の追加
ですが、Bの部品を使用する"H"という部品が親に追加されたとして、同じレベルに同じ子品番が存在する場合もあります。(H部品はBとFの部品で作られるとします。)
H B A C
H B A G
H B A X
H B R
H B X
H F T
H F X
↓
レベル1　H
レベル2　B
レベル3　A
レベル4　C
レベル4　G
レベル4　X
レベル3　R　
レベル3　X　←　重複
レベル2　F
レベル3　T
レベル3　X　←　重複　など重複する場合があります。
>ＶＢＡでＤＡＯ操作するなりテーブルへ書き込んで実施
勉強不足でDAOについてあまり知らないのですが、テーブルへ書き込んで実施とはどのような考え(方法)で簡単になるのでしょうか？
簡単でいいので、教えてもらえると嬉しいです。
毎回、頼りっきりですみません。よろしくお願いします。

回答No.4

noname#79209

#2です。
> レベル1　レベル2　レベル3
> B――――A――――C
> B――――A――――G
> B――――A――――X
> B――――R
> B――――X
だとすると、
> レベル1　B
> レベル2　A
> レベル3　C
> レベル3　G
> レベル3　X
> レベル1　R
> レベル1　X
の下から２行文の
> レベル1　R
> レベル1　X
は
> レベル「2」　R
> レベル「2」　X
じゃないんですか？
それとも、ここだけイレギュラーな付け方なんでしょうか。

それと、最初の質問文にある、
> AはB,G,Xという部品で構成されているとします。
> BはA,R,Xという部品で構成されているとします。
> 親　　子
> A 　　C
> A 　　G
> A　　 X
> B 　　A
> B 　　R
> B 　　X
は
「Aは『C』,G,Xという部品で構成されているとします。」
のタイプミスですよね？
基本的な部分を『正しく』書いていただかないと
回答者は無駄な労力と時間を費やすことになります。

また、各部品絶対的レベルを出したいのか、相対的レベルなのか、
も必要です。

補足

ご指摘有難うございます。
vizzarさんのご指摘どおりです。
> レベル「2」　R
> レベル「2」　X　で間違いでした。
また、Aは『B』→『C』,G,Xという部品で構成～の誤りでした。

>基本的な部分を『正しく』書いていただかないと
>回答者は無駄な労力と時間を費やすことになります。
に対して、おっしゃるとおりだと思います。すみませんでした。
以後、注意をしていきたいと思います。

>各部品絶対的レベルを出したいのか、相対的レベルなのか
との質問に対してですが、知識不足で誤解しているかもしれませんが、例えば、Bの親にHという部品が追加したとしたら、Bがレベル1からレベル2になるのが理想なので、相対的レベルだと思います。
よろしくお願いします。

回答No.3

Dxak

#1です
> レベル1　R
> レベル1　X
は、
レベル2　R
レベル2　X
の誤植だと思って書いたけど・・・誤植じゃないの？

補足

失礼しました。
レベル2　R
レベル2　X
の誤りでした。指摘有難うございます。
今後、質問するの当たってミスをなくす努力したいと思います。
よろしくお願いします。

回答No.2

noname#79209

部品Xが部品AとBの部品となっており、かつ部品AはBの部品ですが
部品Xのレベルはどう判定するのですか？

補足

質問ありがとうございます。レベルのつけ方は、
レベル1　レベル2　レベル3
B――――A――――C
B――――A――――G
B――――A――――X
B――――R
B――――X
を、
レベル1　B
レベル2　A
レベル3　C
レベル3　G
レベル3　X
レベル1　R
レベル1　X
としたいです。引き続き宜しくお願いします。

回答No.1

Dxak

う～ん、ツリー型ＤＢの話ですね
私の場合、ＳＱＬでトライしてアイディアが思い浮かばなくて、ＶＢＡで結果をテーブルに書き込む、若しくは文字列で結果作成する方法で逃げてるパターンです

> B A C
> B A G
> B A X
> B R
> B X
を、
SELECT 部品構成表DB.親 AS 部品_1st,
部品構成表DB.子 AS 部品_2nd,
部品構成表DB_1.子 AS 部品_3rd,
部品構成表DB_2.子 AS 部品_4th
FROM (部品構成表DB LEFT JOIN 部品構成表DB AS 部品構成表DB_1 ON 部品構成表DB.子 = 部品構成表DB_1.親) LEFT JOIN 部品構成表DB AS 部品構成表DB_2 ON 部品構成表DB_1.子 = 部品構成表DB_2.親
WHERE 部品構成表DB.親 Not In (SELECT Tbl_In.子 FROM 部品構成表DB AS Tbl_In);

と言うクエリで作成して、
（「とりあえず部品表」という名前でクエリを作成）

> レベル1　B
> レベル2　A
> レベル3　C
> レベル3　G
> レベル3　X
> レベル1　R
> レベル1　X
を、ユニオンクエリで

SELECT "レベル1" AS レベル, とりあえず部品表.部品_1st AS 部品名
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_1st
HAVING (((とりあえず部品表.部品_1st) Is Not Null))
UNION
SELECT "レベル2" AS レベル, とりあえず部品表.部品_2nd AS 部品名
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_2nd
HAVING (((とりあえず部品表.部品_2nd) Is Not Null))
UNION
SELECT "レベル3" AS レベル, とりあえず部品表.部品_3rd AS 部品名
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_3rd
HAVING (((とりあえず部品表.部品_3rd) Is Not Null))
UNION
SELECT "レベル4" AS レベル, とりあえず部品表.部品_4th AS 部品名
FROM とりあえず部品表
GROUP BY とりあえず部品表.部品_4th
HAVING (((とりあえず部品表.部品_4th) Is Not Null));

で、順番は違いますが出ては来ます
しかし、こう言うのは順番とか色々見た目が重要になるので、並び替えようのインデックスを作るか？
もう一工夫は必要そうだけどね～＾＾；

お礼

考えてくださってありがとうございます。参考にさせていただきます。
有難うございます☆