- ベストアンサー
Access 複数テーブルをまとめる
- Access初心者のため、複数のテーブルを結合して書類IDを抽出する方法について教えてください。
- 顧客テーブルと書類テーブルを結合し、顧客ごとの書類IDを一覧化したいです。
- 選択クエリを使用して顧客ごとの書類IDを一覧化する方法がわかりません。助けてください。
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
＃４です お疲れ様です。 > >sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _ > >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"←この部分は １行目は＃３のもので、２行目は＃２のものになってますね ＃３の記述をもう一度見て頂ければ、VBAで記述している順は、 TbHead ・・・ 新規追加の関数（テーブルとIDの関連付け用） MkTable ・・・ 作成しておいたワークテーブル「TCTT」の内容を作るもの TbInit ・・・ クロス集計用のワークテーブル「TCT」を作り直すもの （作り直す時に TbHead の情報を使用） IdJoin ・・・ カンマ区切りでIDを羅列するもの ＃３に記述した「TCT」の構成を変更し、 ＃３の TbInit を実行してみると、「TCT」の内容は雰囲気以下の様になるはず。 顧客ID 種類 WorkID 1 請求ID 4 1 請求ID 5 2 最終ID 5 1 請求ID 6 1 見積ID 10 2 請求ID 10 2 請求ID 13 1 プランID 20 2 プランID 25 1 最終ID 25 2 プランID 26 1 最終ID 26 2 見積ID 30 1 請求ID 30 この変更した「TCT」を元に、＃３でのクロス集計を実行します。（＃２から記述を変更しています） このクロス集計の実行で、毎回「TCT」を作り直すので、 必要になった時に TbInit を実行するのであれば、 WHERE TbInit() 部分は削除して・・・・っていう使い方自体は変わらない事を言いたかったです。 では、TbInit / IdJoin について、＃３と同じものを以下に再掲載します。 （関数名は＃２と同じですが、中の記述はどちらも変更しています Public Function TbInit() As Boolean Dim sSql As String Dim v As Variant sSql = "DELETE * FROM TCT;" CurrentProject.Connection.Execute sSql For Each v In TbHead sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _ & "SELECT 顧客参照ID, '" & v(1) & "', " & v(1) & " FROM " & v(0) & ";" CurrentProject.Connection.Execute sSql Next TbInit = True End Function Public Function IdJoin(iId As Long, sId As String) As String Dim rs As New ADODB.Recordset Dim sS As String sS = "" rs.Source = "SELECT DISTINCT WorkID FROM TCT " _ & "WHERE 顧客ID = " & iId & " AND 種類 = '" & sId & "';" rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly If (Not rs.EOF) Then sS = rs.GetString(adClipString, , "", ",") sS = Left(sS, Len(sS) - 1) End If rs.Close IdJoin = sS End Function
その他の回答 (7)
- 30246kiku
- ベストアンサー率73% (370/504)
＃７です まだ閉じられていなかったので・・・・ ごめんなさい。 ＃７の最終部分に記述していたコメントについては、VBA 記述内での話です。 そう伝わっていれば、良いのですが・・・ 実現したいことと、VBA記述が一致している/いないは抜きにして （質問の解釈違い等もあるので・・・） VBA を記述・提示する・・・・記述・処理を読んで理解してほしい・・・ 私はこれを１番に考えます。 初心者であろうが XXXX を実現したい・・・VBAが必要そう・・・VBAを提示 （初心者用実現方法集・・・みたいな便利なものがあれば良いと思いますが） （XXXX を実現したい・・・・初心者云々の区別はないと思いますけど） コメントを付けて、それを読まれ、理解したつもり・・・・になってもらっては困る。 （実際には、私は困りませんが） また、コメントはこう付けるものだ・・・と思ってもらっても困る。 私はそう思って、VBAの部分と、説明する部分は分けて回答してます。 これが現状のスタイルになっています。 今回 GetString の話が出てきたので、それを使って回答した例として Accessのデータをテキストファイルで出力する方法を教えてください。 http://okwave.jp/qa/q6003762.html ここでも、VBAの記述と、説明部分は分けてます。 説明が足りなかったかと思った事もありましたが、処理を読まれたんでしょうか？？ なぜそう記述したのか・・・・これに該当する回答例は Accessでチェックボックス選択でレコード追加 http://okwave.jp/qa/q7451970.html の＃５にある Me.天候 = Me.天候 '編集状態にしたいため 部分になるでしょうか。 複数のイベントが絡み合う・・・・ この場合も、VBA記述を読んで理解してほしかったので説明部分は分けてました。 コンボボックス 選択後のカーソル制御 http://okwave.jp/qa/q6966456.html ちなみに、このQAでのVBAの記述・書き方、どれがお好みですか？ 何を持って上達したか・・・・今のところ曖昧状態になってます。 解決されたらOKとみるのか・・・ ベストアンサーにならなかったらNGとみるのか・・・・ （解決されたのなら誰がベストアンサーになっても良いと思ってますけど・・・） 補足が無ければOKとみるのか・・・ 質問者さんの反応が何もなければNGとみるのか・・・ 基本、補足等あれば追加回答していますが、xx 回以上の時はNGとみるのか・・・ 解決に向けて頑張っている方を見放すことはしていなかったと思います。 （私がギブアップしたのなら別ですけど） それだけでもOKとみるのか・・・ 検討課題、ありがとうございました。 いろいろと考えてみます。 なお、一連の回答での記述（VBA、クエリ）の仕方等々、ほんの一例にしかなりません。 以上 頑張ってください （一応解決されたようですので、これを最後にします）
お礼
>30246kikuさん ご返答ありがとうございました。 >まだ閉じられていなかったので・・・・ 何かご意見があればと念のため閉じておりませんでした。 >そう伝わっていれば、良いのですが・・・ どちらとも取れたので、コメントさせて頂きました。 >VBA を記述・提示する・・・・記述・処理を読んで理解してほしい・・・ >私はこれを１番に考えます。 これはご最もだと私も思います！ >（初心者用実現方法集・・・みたいな便利なものがあれば良いと思いますが） これいいですね☆ >コメントを付けて、それを読まれ、理解したつもり・・・・になってもらっては困る。 >また、コメントはこう付けるものだ・・・と思ってもらっても困る。 できる方たちにとっては、自分で理解してから納得してと言うのも当然！ 確かにそう思います！ですが、これは人それぞれかと… 今後のことも考え、必死に理解しようとする人と今だけ解決できればと言う人は存在しますから… 私は前者です！ >また、コメントはこう付けるものだ・・・と思ってもらっても困る。 これはあまり思う方は少ないと感じます。 ただコメントと説明に関して、本当に初心者は読んでもチンプンカンプンです… 全くの初心者（誰に教えて貰うことのできない環境）は、それを理解しようと考え、本読んだり、調べたりして、 少しづつ少しづつ理解をしていくんです。（しかも本業ではないところで必要な場合が多いので…） いくつも処理のご提示ありがとうございます！ とても参考になります！ >説明が足りなかったかと思った事もありましたが、処理を読まれたんでしょうか？？ いえ！足りないなんてとんでもない！ 処理は読んだというよりは、ひとつひとつ試して、この処理はこう動くんだなとか ここの部分はこういう意味があるんだとか理解するようにしてます。 >ちなみに、このQAでのVBAの記述・書き方、どれがお好みですか？ どれもわかり易いですが、あえて言うのであれば、2番目ですかね。 初心者に対してわかり易くという点で考えると、最初にこういう風に動かすというある程度の説明があり、 VBA記述 最後に補足と違った処理をする場合は~の例をといった感じがわかりやすですかね。 沢山の方々が言うのですが、過去記事は見ましたか？ということ。これは初心者にとっては見たとしか言えません。 え？見てないじゃんって思うかと思いますが、まず初心者は処理の言い方がわかりません！なので、検索方法（検索クエリのキーワード）が乏しいのです… だから自分でわかる範囲での、キーワードでしか検索できていないのですね… >何を持って上達したか・・・・今のところ曖昧状態になってます。 私の中では、解決したらOKだと思います。もちろん質問者さんが感謝してくれたらの話です。 なので、回答に対してありがとうと言われればいいのだと感じます。 >検討課題、ありがとうございました。 >いろいろと考えてみます。 こんな私に丁寧にご教示を頂いた上に、初心者の戯言にもお付き合い頂いて、 本当にありがとうございました！ >なお、一連の回答での記述（VBA、クエリ）の仕方等々、ほんの一例にしかなりません。 これが奥が深く、私はまだまだと感じる点ですね… 解決できましたし、30246kikuさんのお蔭で、もう少しで完成になりそうです☆ 頑張ってVBAを少しでも理解できるように努力していきます！ また何かあれば、その際はまたご教示の程 よろしくお願い申し上げますm(__)m 本当に心より感謝しております。 ありがとうございました！！！
- 30246kiku
- ベストアンサー率73% (370/504)
＃６です 動きましたか・・・説明不足もあってどうかと思っていましたが・・・ （VBA記述の処理については、ほとんど触れていなかったですね） （理解されてから使ってみてください） （１） ワークテーブル「TCTT」の内容ですが、MkTable を実行するまで書き変わることはありません。 実行のタイミングで、TbHead に記述されたテーブル、IDを参照して作り直します。 例えば「Tプラン」で、プランIDを追加/変更等行っても、「TCTT」内が変わることはありません。 「TCTT」を見たい時に、MkTable を実行してから見る・・・この手順が良いと思います。 「Tプラン」を変更したから・・・「T見積」を変更したから・・・ その都度、MkTable を実行・・・あまり意味が無いように思います。 というのは、「TCTT」の内容ではデータベースとしては使えない（使いにくい）内容です。 見るため・・・の用途に絞った方が良いのかと思います。 なお、「TCTT」名を変更したら、修正は MkTable 内の２か所だけです。 （２） クロス集計クエリ内に、WHERE TbInit() の記述がある限り、その時点での情報が「TCT」に作成されるので、常に最新のものを見ることが出来ます。 WHERE TbInit() の記述を削除すると、「TCT」の内容は TbInit を実行するまで書き変わりません。 なので、「TCTT」同様自動で書き変わることはありません。 通常のクロス集計では中間のテーブル（今回の「TCT」）を作ることはしません。 当初、「T書類1」～「T書類4」の様な連番のテーブルは、１つのテーブルにして・・・ その事を１番言いたくて、「TCT」の構成を提示しました。 もし、１つのテーブルになったのなら TbInit 自体不要になるものです。 その構成になった場合、クロス集計が楽なのか・・・・ その部分だけを回答するのも何だから（１）についても書いておこうかと・・・ なので、テーブル名も「TC」のテンポラリ「TCT」、「TCT」は使ったから次は「TCTT」 （１）について記述しておいてよかったなぁ・・・と＃１さんのお礼を見て思いました。 というのは、Excelで作って、どうやったら綺麗にインポートできるんだろうか・・・ こっちの方が方法を思いつきませんでした。 （２）について、DJoin でもできたという事なので、DJoin であれば、その時点での最新のものを表示できます。 楽だと思います。 ※ 参考までに クロス集計で、中間テーブルを使わずに今回のことをやろうとした場合、UNION を使った感じになると思います。 （提示してきていた「TCT」は、事前に UNION した内容を作っておきましょう・・・というものになります） 中間テーブルを使わないので、最新の情報で表示されます。 標準モジュールに以下を記述しておきます Public Function IdJoin2(iId As Long, sTn As String, sId As String) As String Dim rs As New ADODB.Recordset Dim sS As String sS = "" rs.Source = "SELECT DISTINCT " & sId & " FROM " & sTn _ & " WHERE 顧客参照ID = " & iId & ";" rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly If (Not rs.EOF) Then sS = rs.GetString(adClipString, , "", ",") sS = Left(sS, Len(sS) - 1) End If rs.Close IdJoin2 = sS End Function クロス集計のクエリとして以下を記述します。 TRANSFORM First(IdJoin2(T顧客.顧客ID,Q1.TN,Q1.TID)) AS 値 SELECT T顧客.顧客ID, First(T顧客.名前) AS 名前 FROM T顧客 INNER JOIN ( SELECT DISTINCT 顧客参照ID, "Tプラン" AS TN, "プランID" AS TID FROM Tプラン UNION ALL SELECT DISTINCT 顧客参照ID, "T見積", "見積ID" FROM T見積 UNION ALL SELECT DISTINCT 顧客参照ID, "T最終", "最終ID" FROM T最終 UNION ALL SELECT DISTINCT 顧客参照ID, "T請求", "請求ID" FROM T請求 ) AS Q1 ON T顧客.顧客ID=Q1.顧客参照ID GROUP BY T顧客.顧客ID PIVOT Q1.TID; このクロス集計より DJoin の方が楽ですよね・・・・ ※ 余談 Web検索等で、使えそうな（使える）記述を見ることがあります。 それを使おうとした時、自分で理解して・・・最低限必要なことと思います。 例えば、今回 IdJoin で以下の記述をしていました。 sS = rs.GetString(adClipString, , "", ",") sS = Left(sS, Len(sS) - 1) これは、抽出したレコードセットで、 ・フィールド間の区切りを "" ・レコード間の区切りを "," （カンマ１文字で） 実際に抽出したフィールドは１つしかないので、"" 指定はあってもなくても レコード間の区切りは必ず付加されるので、一番最後は不要だから Left で１文字削除したものにします。 ただ、"," （カンマ１文字）を指定していたので -1 なだけで、２文字指定していたら -2 です。 もし、この区切り文字を変数 sDel に持っていていたとしたら sDel = "," sS = rs.GetString(adClipString, , "", sDel) sS = Left(sS, Len(sS) - Len(sDel)) になると思います。 専用・汎用は、それなりに難しいですね。 ※ 今回の反省 ・＃２で、（１）（２）用のVBAを記述し、標準モジュールに・・・と書いてました。 モジュールを分けて確認されていた・・・これ、わかりませんでした。 ・＃３で Private の関数を作ったので、一気にコピー＆貼り付けできるようにVBAをまとめて書いていましたが、上記で分けた部分部分で処理されていたとは思いませんでした。 １つのモジュールに記述する・・・・説明不足でした ・テーブル名等が連番ではない事がわかった時点で、（２）のクロス集計について引っ込めれば良かったのかも （でも、動かないものではないので・・・・で、納得させてました） ・＃２でテンポラリテーブルと表現していましたが、（意味的に）使い終わったら削除する・・・・これが強いのか・・・と思って＃３以降は、ワークテーブルと表現を変えてました。（あまり意味無いのかもしれませんが） いろいろいじってみてください。 失礼しました。 関数内の処理について説明が必要であれば、補足してください。 コメントについては、いろいろな考え方があると思います。 私はコメントをあまり書かない派です。 読めばわかることは、書きません。 何故そうやったか・・・理由はよく書きます。 処理を読む時、邪魔するコメントもあります。 （そんなの処理を読めばわかるよ・・・） 処理を変えたけど、古いままのコメントがあったりします。 （なにやってんだか・・・・） コメントを読むのに慣れない方が良いと私は思います。
お礼
>30246kikuさん 返信が遅れ、大変申し訳ございませんでした。 ご丁寧にご回答ありがとうございます！ 疑問点が解決できました！ 本当にありがとうございましたm(__)m 本当に丁寧に教えて下さり、感謝感激です！（涙） 30246kikuさんの反省点についてですが、 まず私のような初心者がこんなにも難しいことをやろうとしている時点で間違っておりますよね… コメントについてですが、 あくまで私個人の見解を述べさせて頂きます。 まず、このようなQ＆Aサイトに質問をする人には2種類いると考えます。 1つ目は知識が乏しく、仕組みなどをあまり理解していないケース。 こちらは仕事上でどうしても必要なのだが、理解しようにも理解ができない状態。 業務上で依頼されたものを作成するために質問をしているので、自分自身が必要な情報を全て得たいと考えている。 2つ目はある程度の知識を持っているが、解決できないものがあったので、質問するケース。 こちらは仕事上などはあまり関係なく、少しのヒント（違った見解）が貰えればそれでよいと考えている。 ちなみに私は前者です… 言い訳をするというのであれば、他の前者の方々もそうだと思いますが、 業務上でどうしても必要なのに、何をやればいいかすらわからない（知識がない為）、 でも絶対にやらなければならず、「できません」があり得ない。しかも納期が迫っている… そうなった場合、何が何でも解決しようとするものの、今から勉強して作り上げるのはほぼ不可能な為、 知識のある方々に教えを乞う。「全て教えて下さい！少しヒントを貰ったとしても解決しようがないから…」となるわけです… 生意気なことを言うようですが、上達という観点でみれば、全く知識のない方にもわかるように説明ができなければならないと考えます。 知識や技術だけを養っていても、新しいものはどんどんを出てきますし、追いついていけなくなるのが実情だと思います。（若い世代も出てきます） だったらSEなどの人をまとめ上げることを目指していかなければと感じます。 そのようにならなければ、知識だけある頭でっかちのただのオタクになってしまいますよね？ 30246kikuさんはコメントをあまり書かない派とおっしゃっておりましたが、それでは勿体ないと思います。 そう感じたのは、私のような知識がない人にここまで丁寧にご教示頂けたからです。 上記を踏まえ、検討をしてみて下さい。 前者の場合はコメントも含め丁寧に教えてあげる。 後者の場合はいくつかの見解を示すだけ。 そうすれば私以外にも同じように悩んでいる方々にも助けにもなりますし、 少しでも上達をと考えられている30246kikuさんにも良いかと思います。 生意気な意見をしたので、 もしご気分を悪くされたのであれば、大変申し訳ございません。 ただの初心者の戯言だと思って流して下さい。 今回、とても細かくご丁寧にご教示を頂きまして、 本当に感謝しております！ 本当に本当にありがとうございましたm(__)m
- 30246kiku
- ベストアンサー率73% (370/504)
＃５です やっぱり説明は苦手ですね。 少しでも上達したいと思って回答するようになったのですが・・・ > クエリでエラーが… > 「複数値を持つフィールドを使用した操作は無効」と出てしまいます！ これについて、全くわかりません。また、推測できません。 お手数ですが、以下手順で確認していただけませんか。 ・新規mdb or accdb を作成します ・テーブル「T顧客」「T見積」「T請求」「Tプラン」「T最終」をインポートします。 （インポート元は、今までいじっていたもので良いと思います） ・ワークテーブル「TCTT」を作成します。 「an」（オートナンバ：主キー） 「顧客ID」「名前」「見積ID」「請求ID」「プランID」「最終ID」 「名前」以外は数値型：長整数 「an」以外は、値要求：いいえ としておきます。 ・ワークテーブル「TCT」を作成します。 「顧客ID」「種類」「WorkID」の３つ 「顧客ID」「WorkID」は数値型：長整数 「種類」はテキスト型 ・VBEの画面を表示します。 ・ ADO を使えるように、参照設定で Microsoft ActiveX Data Objects x.y Library 追加 （私の確認時、x.y は 2.5 を選んでました） ・挿入で標準モジュールを選択し、そこに＃３の VBA 全部を転記（コピー＆貼り付けで） ・VBAの MkTable を実行し、「TCTT」内がデータで埋まるか確認 ・VBAの TbInit を実行し、「TCT」が出来上がるか確認 ・クエリのデザイン（SQLビュー）で、＃３と同じ以下を転記（コピー＆貼り付けで） TRANSFORM First(IdJoin(T顧客.顧客ID,TCT.種類)) AS 値 SELECT T顧客.顧客ID, First(T顧客.名前) AS 名前 FROM T顧客 INNER JOIN TCT ON T顧客.顧客ID = TCT.顧客ID WHERE TbInit() GROUP BY T顧客.顧客ID PIVOT TCT.種類; 表示を「データシートビュー」にして表示結果を確認 ここまでの手順で、そこそこそれっぽく動いている事が確認できると思います。 ※ 教えてgoo は、何か変更とかしたのでしょうか？ 回答内容をコピー＆貼り付けすると、行頭に余計なスペースが入りますね。 okwave とか、他の提携サイトを使ってコピー&貼り付けした方が良いと思います。 > DJoinの方も試したのですが、「式に未定義関数'DJoin'がある」と出てきてしまいます… > 定義の方法がわかりません… ＃１さんがURL記述されていますよね。 DJoin を使用する場合、 そこの記事に書かれている DJoin の処理・記述を組み込まなくてはなりません。 簡単には、標準モジュールにコピー＆貼り付けすれば使えると思います。 申し訳ありませんが、今までの操作は一旦忘れて頂いて、上記順で確認頂けませんか。 以上 よろしくお願いいたします。 > 更にはテーブルを作成する標準モジュールをイベント発生にするにはどうすればよいでしょうか？ については、どう解釈してよいのか迷ってますが、 ボタンがクリックされた時に、MkTable を実行という事で良いですか。であれば以下で Private Sub ボタン_Click() Call MkTable End Sub
お礼
>30246kikuさん ご回答ありがとうございます！ 全てできました！！！！！ 本当に感謝しておりますm(__)m 説明が苦手なんて…とんでもない！ 本当に丁寧に教えて下さり、神です（涙） >少しでも上達したいと思って回答するようになったのですが・・・ 向上心が素晴らしいです！私も見習いたいと思います！！！ できたなかった原因として、テストの為に標準モジュールに幾つも同じようにコードを記入していたので、重複してエラーが出ていただけでした… ご面倒をお掛けし、申し訳ございませんでしたm(__)m >※ 教えてgoo は、何か変更とかしたのでしょうか？ 私はokwaveから投稿しております。 >DJoin を使用する場合、 こちらもできました！丁寧にご教示頂いたお蔭です！ >> 更にはテーブルを作成する標準モジュールをイベント発生にするにはど >うすればよいでしょうか？ >については、どう解釈してよいのか迷ってますが、 >ボタンがクリックされた時に、MkTable を実行という事で良いですか。であ >れば以下で こちらについてですが、各テーブルの値が更新されれば、TbInit / IdJoinを使用したクエリ「Q_TC」には反映されるのですか？ 一度「MkTable」を作成してしまえば、わざわざまた「MkTable」を実行し作成しなくても、値は反映されております。 どういう仕組みになっているかが、あまり理解できておりません… >MkTable ・・・ 作成しておいたワークテーブル「TCTT」の内容を作るもの ということは各テーブルの値が更新されたらまた作成する必要がありますよね？ >TbInit ・・・ クロス集計用のワークテーブル「TCT」を作り直すもの > （作り直す時に TbHead の情報を使用） ということはクエリ「Q_TC」を開いた時点で「TCT」が更新さえて、値が抽出できるということですね？ 知識が乏しく、大変申し訳ございません… 最後にご教示頂けませんでしょうか？ よろしくお願い申し上げますm(__)m
- 30246kiku
- ベストアンサー率73% (370/504)
＃３です 時間がたっていたので、解決されていたでしょうか。 > ですが、（２）のワークテーブル「TCT」作成の段階 > >TbInit() / IdJoin の使用方法は以前の通りです。 > と頂きましたが、 > >sSql = "INSERT INTO TCT(顧客ID, 種類, 書類ID) " _ > >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"」 > の部分はどのように記載すれば宜しいでしょうか？ 上記の sSql 作成部分のものは、＃２での「書類1ID」～「書類4ID」の時のものです。 ＃３では、「TCT」の構成も変更しています。 この辺は、＃３を再度見てください。 特に、 > >TbInit() / IdJoin の使用方法は以前の通りです。 と書いてましたが、使い方は一緒ですが、記述を変更しています。 ＃２、＃３見比べて頂ければと・・・・・ ＃２での記述は捨てて頂いて、＃３でやってみてください。 なお、関数名とかワークテーブル名とか、適宜変更してください。
お礼
>30246kikuさん ご回答ありがとうございます！ ずっと悩み続けております…(ToT;) >上記の sSql 作成部分のものは、＃２での「書類1ID」～「書類4ID」の時 >のものです。 ご教示頂いた内容で変更しております。。。 >sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _ >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"←この部分はTCｘの書類ｘIDを「For i = 1 To 4」でｘ値1～4まで持ってくるという認識で間違いないでしょうか？ だとすれば、この部分の記述を変更すればと思い、いろいろ試しましたが上手くいかず…知識が乏しくてお恥ずかしいです… T見積,見積ID、T請求, 請求ID、Tプラン,プランID、T最終,最終IDと全てのテーブルとID名が違う時の記述がどうしてもわからずです… お忙しいところ、大変ご面倒をお掛けいたしますが、ご教示頂けませんでしょうか？ 何卒、よろしくお願い申し上げますm(_ _)m
- 30246kiku
- ベストアンサー率73% (370/504)
＃２です おっと、補足の通知があったのね・・・で、その内容も含めてみました。 テーブル名等、連番じゃなかったんですね。 では、まず標準モジュールに記述する内容全てを以下に（参考程度にしてください） 拙い説明は後で Private Function TbHead() As Variant TbHead = Array( _ Array("T見積", "見積ID"), _ Array("T請求", "請求ID"), _ Array("Tプラン", "プランID"), _ Array("T最終", "最終ID") _ ) End Function Public Sub MkTable() Dim sSql As String Dim rsK As New ADODB.Recordset Dim rsTo As New ADODB.Recordset Dim rsS As New ADODB.Recordset Dim vBookMark As Variant Dim v As Variant sSql = "DELETE * FROM TCTT;" CurrentProject.Connection.Execute sSql rsTo.Open "TCTT", CurrentProject.Connection, adOpenKeyset, adLockOptimistic rsK.Source = "SELECT 顧客ID, 名前 FROM T顧客;" rsK.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly While (Not rsK.EOF) rsTo.AddNew rsTo("顧客ID") = rsK("顧客ID") rsTo("名前") = rsK("名前") rsTo.Update vBookMark = rsTo.Bookmark For Each v In TbHead rsTo.Bookmark = vBookMark rsS.Source = "SELECT DISTINCT " & v(1) & " FROM " & v(0) _ & " WHERE 顧客参照ID = " & rsK("顧客ID") & ";" rsS.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly While (Not rsS.EOF) If (rsTo.EOF) Then rsTo.AddNew rsTo(v(1)) = rsS(0) rsTo.Update rsTo.MoveNext rsS.MoveNext Wend rsS.Close Next rsK.MoveNext Wend rsK.Close rsTo.Close End Sub Public Function TbInit() As Boolean Dim sSql As String Dim v As Variant sSql = "DELETE * FROM TCT;" CurrentProject.Connection.Execute sSql For Each v In TbHead sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _ & "SELECT 顧客参照ID, '" & v(1) & "', " & v(1) & " FROM " & v(0) & ";" CurrentProject.Connection.Execute sSql Next TbInit = True End Function Public Function IdJoin(iId As Long, sId As String) As String Dim rs As New ADODB.Recordset Dim sS As String sS = "" rs.Source = "SELECT DISTINCT WorkID FROM TCT " _ & "WHERE 顧客ID = " & iId & " AND 種類 = '" & sId & "';" rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly If (Not rs.EOF) Then sS = rs.GetString(adClipString, , "", ",") sS = Left(sS, Len(sS) - 1) End If rs.Close IdJoin = sS End Function 使用するテーブルは「T顧客」「T見積」「T請求」「Tプラン」「T最終」 「T顧客」以外の、テーブル名、ID名の関連付けを 新設関数 TbHead() にて （テーブル名は違うが、ID名に重複がある場合にはこの方法は使えません） （１）を実現する為のワークテーブル「TCTT」項目名変更 「an」（オートナンバ：主キー） 「顧客ID」「名前」「見積ID」「請求ID」「プランID」「最終ID」 「名前」以外は数値型：長整数 「an」以外は、値要求：いいえ としておきます。 処理するテーブルが増えた場合、 ・ここのテーブルのフィールドを追加する ・関数 TbHead 内を修正する この２つを忘れずに行ってください。 そして、MkTable を実行します。 （２）ワークテーブル「TCT」を変更します。 構成を「顧客ID」「種類」「WorkID」の３つに変更します。 「顧客ID」「WorkID」は数値型：長整数 「種類」はテキスト型に変更します。 TbInit() / IdJoin の使用方法は以前の通りです。 クエリの内容を以下に変更します。 TRANSFORM First(IdJoin(T顧客.顧客ID,TCT.種類)) AS 値 SELECT T顧客.顧客ID, First(T顧客.名前) AS 名前 FROM T顧客 INNER JOIN TCT ON T顧客.顧客ID = TCT.顧客ID WHERE TbInit() GROUP BY T顧客.顧客ID PIVOT TCT.種類; これらを実行してみた結果の表示は、添付図の通りです。 なお、クロス集計での結果の表示（列部分の並び）は、 ワークテーブル「TCT」の「種類」の昇順で右に並びます。 並びを固定したい場合には、クエリの最終行を変更します。 PIVOT TCT.種類; ↓ PIVOT TCT.種類 IN ("見積ID","請求ID","プランID","最終ID"); とすると、書いた順で並びますが、処理するテーブルを増やしたとかした場合、 テーブル「TCTT」、関数 TbHead の変更と一緒にクエリも変更する必要があります。 なお、DJoin を使用したとすると以下かな（未検証） DJoin を使ったクエリのSQLビューでの記述は以下の様になると思います。（未検証） SELECT 顧客ID, 名前 , DJoin("見積ID","T見積","顧客参照ID = " & 顧客ID) AS 見積ID , DJoin("請求ID","T請求","顧客参照ID = " & 顧客ID) AS 請求ID , DJoin("プランID","Tプラン","顧客参照ID = " & 顧客ID) AS プランID , DJoin("最終ID","T最終","顧客参照ID = " & 顧客ID) AS 最終ID FROM T顧客; （２）については、DJoin が楽だと思います。 ※ 内容を理解した上で、責任もって・・・・ （わからない部分については、補足いただければ・・・・わかる範囲で回答可） ただ、（１）の様な使い方はあまりやりませんかね （やってできないことはない・・・・っていうレベルのものかも）
お礼
>30246kikuさん ご回答ありがとうございます！ （１）出来ました！感激です！！！ 拙い説明なんてとんでもない！わかりやすくて大変参考になります！ ですが、（２）のワークテーブル「TCT」作成の段階 >TbInit() / IdJoin の使用方法は以前の通りです。 と頂きましたが、 >sSql = "INSERT INTO TCT(顧客ID, 種類, 書類ID) " _ >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"」 の部分はどのように記載すれば宜しいでしょうか？ 知識不足で大変申し訳ございませんm(__)m 引き続きのご教示をよろしくお願い申し上げます。
- 30246kiku
- ベストアンサー率73% (370/504)
では、ゴリゴリ記述してみます。（以下参考まで） 確認した時の環境は、 テーブル「TC」 （「Ｔ顧客」と同じ） テーブル「TC1」～「TC4」 （「Ｔ書類1」～「Ｔ書類4」と同じ） （１）の場合、テンポラリテーブル「TCTT」を使用 テーブル「TCTT」の構成は、 「an」（オートナンバ：主キー） 「顧客ID」「名前」「書類1ID」「書類2ID」「書類3ID」「書類4ID」 「名前」以外は数値型：長整数 「an」以外は、値要求：いいえ としておきます。 ・オートナンバは、登録した順/表示する順のために必要になります。 ADO を使用できる状態に（参照設定）してから、以下を標準モジュールに記述し実行します。 Public Sub MkTable() Dim sSql As String Dim rsK As New ADODB.Recordset Dim rsTo As New ADODB.Recordset Dim rsS As New ADODB.Recordset Dim vBookMark As Variant Dim i As Integer sSql = "DELETE * FROM TCTT;" CurrentProject.Connection.Execute sSql rsTo.Open "TCTT", CurrentProject.Connection, adOpenKeyset, adLockOptimistic rsK.Source = "SELECT 顧客ID, 名前 FROM TC;" rsK.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly While (Not rsK.EOF) rsTo.AddNew rsTo("顧客ID") = rsK("顧客ID") rsTo("名前") = rsK("名前") rsTo.Update vBookMark = rsTo.Bookmark For i = 1 To 4 rsTo.Bookmark = vBookMark rsS.Source = "SELECT DISTINCT 書類" & i & "ID FROM TC" & i _ & " WHERE 顧客ID = " & rsK("顧客ID") & ";" rsS.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly While (Not rsS.EOF) If (rsTo.EOF) Then rsTo.AddNew rsTo("書類" & i & "ID") = rsS(0) rsTo.Update rsTo.MoveNext rsS.MoveNext Wend rsS.Close Next rsK.MoveNext Wend rsK.Close rsTo.Close End Sub テーブルに内容が出来上がるので、クエリで「an」を表示しないようにするとか・・・ （２）＃１さん紹介の方法が簡単かと思いますが、違う方法でやってみます。 クロス集計を使ってみようか・・・というもの（こっちの方がちょっと複雑です） テンポラリテーブル「TCT」を使用します。 構成は「顧客ID」「種類」「書類ID」の３つ（いずれも数値型：長整数） 「顧客ID」「種類」には、重複ありのインデックスを設定 以下を標準モジュールに記述しておきます。 Public Function TbInit() As Boolean Dim sSql As String Dim i As Integer Dim v As Variant sSql = "DELETE * FROM TCT;" CurrentProject.Connection.Execute sSql For i = 1 To 4 sSql = "INSERT INTO TCT(顧客ID, 種類, 書類ID) " _ & "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";" CurrentProject.Connection.Execute sSql Next TbInit = True End Function Public Function IdJoin(iId As Long, iNum As Long) As String Dim rs As New ADODB.Recordset Dim sS As String sS = "" rs.Source = "SELECT DISTINCT 書類ID FROM TCT " _ & "WHERE 顧客ID = " & iId & " AND 種類 = " & iNum & ";" rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly If (Not rs.EOF) Then sS = rs.GetString(adClipString, , "", ",") sS = Left(sS, Len(sS) - 1) End If rs.Close IdJoin = sS End Function TbInit() の実行で、テンポラリテーブル「TCT」は以下の様になります。 顧客ID 種類 書類ID 1 1 10 2 1 30 1 2 4 1 2 5 1 2 6 1 2 30 2 2 10 2 2 13 1 3 20 2 3 25 2 3 26 1 4 25 1 4 26 2 4 5 「種類」のところは、書類xID の x の値になるようにします。 （「Ｔ書類1」～「Ｔ書類4」のフィールドが同じなら、この構成に近い方が後々楽なのかも） IdJoin でやっている事は、DJoin とほぼ同じですが、専用にしている分、簡単になります。 クエリを SQLビューで表示しておいて、以下を記述します。（確認時「Q_TC」名） TRANSFORM First(IdJoin(TC.顧客ID,TCT.種類)) AS 値 SELECT TC.顧客ID, First(TC.名前) AS 名前 FROM TC INNER JOIN TCT ON TC.顧客ID=TCT.顧客ID WHERE TbInit() GROUP BY TC.顧客ID PIVOT "書類" & TCT.種類 & "ID"; このクロス集計クエリの実行のたびに、テーブル「TCT」の内容は作り直されます。 都度作り直したくない場合は、WHERE TbInit() 部分を削除してください。 そして、必要になった時に TbInit() を実行してください。 それぞれを実行してみた結果は、添付図の様になります。 ※ 余談 DJoin を使ったクエリのSQLビューでの記述は以下の様になると思います。（未検証） SELECT 顧客ID, 名前 , DJoin("書類1ID","Ｔ書類1","顧客ID = " & 顧客ID) AS 書類1ID , DJoin("書類2ID","Ｔ書類2","顧客ID = " & 顧客ID) AS 書類2ID , DJoin("書類3ID","Ｔ書類3","顧客ID = " & 顧客ID) AS 書類3ID , DJoin("書類4ID","Ｔ書類4","顧客ID = " & 顧客ID) AS 書類4ID FROM Ｔ顧客; いずれにせよ、何をやっているのかを理解された上で、使用された方が良いと思います。 なお、 > その他に複数同じような構造のテーブルがあります。 という事なので、For i = 1 To 4 の 4 を変更していく等、微調整は必要です。
お礼
>30246kikuさん ご回答ありがとうございました！ 素晴らしいです！まさに添付図のようにしたかったのです！！！ しかし…今ご教示頂いたもので作成しようとしていたのですが、 本来、私が作成している内容は、 Ｔ顧客…顧客ID(主キー)、名前、… Ｔ見積…顧客ID(重複キー有)、見積ID、… Ｔ請求…顧客ID(重複キー有)、請求ID、… Ｔプラン…顧客ID(重複キー有)、プランID、… Ｔ最終…顧客ID(重複キー有)、最終ID、… ・ ・ ・ となっているのです… 質問事項でわかり易いようにと思い、簡素にして質問してしまいました。 やりたいことはまさにご教示頂いたまんまなのですが、この場合ですとどのように記述すればよいのかがわかりません… 大変ご面倒をお掛けし、申し訳ありませんが 今一度、ご教示の程 よろしくお願い申し上げますm(__)m
補足
申し訳ございません… 更に「Ｔ顧客」以外のテーブルは「顧客参照ID」となっていて、「Ｔ顧客」からルックアップで値を持ってきております…テキスト型ではなく「数値型：長整数」 Ｔ顧客…顧客ID(主キー)、名前、… Ｔ見積…顧客参照ID(重複キー有、数値型で表示はテキスト)、見積ID、… Ｔ請求…顧客参照ID(重複キー有、数値型で表示はテキスト)、請求ID、… Ｔプラン…顧客参照ID(重複キー有、数値型で表示はテキスト)、プランID、… Ｔ最終…顧客参照ID(重複キー有、数値型で表示はテキスト)、最終ID、… 初期の作成過程で本を見ながら作成していたので、後のことをあまり考えずに作成しておりました… ご教示をお願い申し上げますm(__)m
- nicotinism
- ベストアンサー率70% (1019/1452)
>(1)一つのテーブルかクエリで抽出 相当に面倒だと思います。VBAでゴリゴリとテーブルに書き込んでゆくしか無いような？ 妥協案として、レポートでなら重複レコード非表示に設定すれば近いことは出来そうです。 http://www.sanryu.net/acc/tips/tips236.htm >(2)更にまとめたものにしたいです。 こちらも厄介です。 YU-TANG さんと Gin_II さんが http://www.f3.dion.ne.jp/~element/msaccess/AcTipsVbaDJoin.html http://www.f3.dion.ne.jp/~element/msaccess/DJoinADObyGin_II.html DJoin関数を提供されていますが意味を理解しアレンジするのはかなり難しいかと思います。
お礼
>nicotinismさん 回答ありがとうございます！ 難しいのですね… アクセスが無理であれば、エクセルで一度データを吐き出して、細々と関数を使って整理していき、最終的なデータになれば、エクセルからアクセスに戻せば…？ なんてことを考えておりますが、そんなことできますかね？？？ 非常に苦戦しております…お知恵をお貸し下さい… よろしくお願い申し上げますm(__)m
お礼
>30246kikuさん ご回答ありがとうございます！ ご丁寧にご説明を頂き、本当に感謝しております！ >For Each v In TbHead 部分の「TbHead」で、「変数が定義されてません」とエラーがでてしまいます… どうすればよいでしょうか？ 何度も何度も大変申し訳ございません… ご教示頂きながらも必死に内容を理解しようと頑張ってはいますが、独学にてほんの少しだけ理解しているだけの状態で、こんな無謀なことをしようとする時点で間違っていますよね… お忙しいところ、大変ご面倒をお掛けいたしますが、 もう少しだけお付き合い頂けませんでしょうか？ 引き続き、何卒 よろしくお願い申し上げますm(_ _；)m
補足
>30246kikuさん 下記の「お礼入力」で記載いたしましたが、 テーブルできました！！！ 何を勘違いしていたのか…いろいろとゴチャゴチャにしてました… 本当に申し訳ございませんでしたm(__)m テーブルは完成したのですが、 クエリでエラーが… 「複数値を持つフィールドを使用した操作は無効」と出てしまいます！ どうすればよいでしょうか？ DJoinの方も試したのですが、「式に未定義関数'DJoin'がある」と出てきてしまいます…定義の方法がわかりません… 本当に知識がなく、申し訳ないです… 更にはテーブルを作成する標準モジュールをイベント発生にするにはどうすればよいでしょうか？ 「Private Sub」で作ればよいのでしょうか？ 本当にいろいろご迷惑ばかりお掛けし、大変申し訳ございませんm(__)m 何卒、何卒、ご教示お願い申し上げますm(_ _)m