dポイントプレゼントキャンペーン実施中!

こんにちは!

過去記事を検索しましたが、該当がなく、苦戦しております…

お知恵をお貸し下さいm(__)m


テーブル
T顧客…顧客ID(主キー)、名前、…
T書類1…顧客ID(重複キー有)、書類1ID、…
T書類2…顧客ID(重複キー有)、書類2ID、…
T書類3…顧客ID(重複キー有)、書類3ID、…
T書類4…顧客ID(重複キー有)、書類4ID、…
 ・
 ・
 ・
その他に複数同じような構造のテーブルがあります。
テーブル内には他に様々なフィールドがありますが、同一内容の結合できるフィールドは上記のみです。

上記のテーブルですべての書類IDを取ってきたいのですが、
全テーブルを顧客IDと結合し、選択クエリで抽出すると
    
顧客ID | 名前 | 書類1ID | 書類2ID | 書類3ID | 書類4ID
 1  |  Aさん |  10  |  5 |  20  |  26
 1  |  Aさん |  10  |  30 |  20  |  25
 1  |  Aさん |  10   |  4 |  20  |  26
 1  |  Aさん |  10   |  6 |   20  |  25
 2  |  Bさん |  30  |  13 |  25  |  5
 2  |  Bさん |  30   |  10 |  26  |  5
 2  |  Bさん |  30   |  13 |  26  |  5
 2  |  Bさん |  30  |  10 |  25  |  5

のような値が出てきてしまいます…当たり前の結果ですが…


やりたいこととして、
(1)一つのテーブルかクエリで抽出
顧客ID   名前   書類1ID   書類2ID   書類3ID   書類4ID
 1     Aさん   |  10  |  5  |  20  |  26
              |     |  30  |     |  25
              |     |  4
              |     |  6  
 2     Bさん   |  30  |  13  |  25  |  5
              |     |  10  |  26  

のように重複をなくし、
(2)更にまとめたものにしたいです。
顧客ID   名前   書類1ID   書類2ID   書類3ID   書類4ID
 1     Aさん     10     5,30,4,6     20     25,26
 2     Bさん     30      10,13     25,26     5

最終的に上記にまとまったものをフォームにして、顧客IDごとに抽出し、
顧客ごとの各書類のIDはこれとこれですよ!みたいにしたいのです!

説明が下手で申し訳ありませんm(__)m

Access初心者で、独学でコツコツやっているので、知識が乏しいです…
調べてみるとユニオンクエリとかでできるような記事はありますが、
SQL文なども理解ができずに苦戦しております…


お手数ですが、ご教示の程 よろしくお願い申し上げます。

A 回答 (8件)

#4です



お疲れ様です。

> >sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _
> >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"←この部分は

1行目は#3のもので、2行目は#2のものになってますね
#3の記述をもう一度見て頂ければ、VBAで記述している順は、

TbHead ・・・ 新規追加の関数(テーブルとIDの関連付け用)
MkTable ・・・ 作成しておいたワークテーブル「TCTT」の内容を作るもの
TbInit ・・・ クロス集計用のワークテーブル「TCT」を作り直すもの
      (作り直す時に TbHead の情報を使用)
IdJoin ・・・ カンマ区切りでIDを羅列するもの

#3に記述した「TCT」の構成を変更し、
#3の 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」を元に、#3でのクロス集計を実行します。(#2から記述を変更しています)
このクロス集計の実行で、毎回「TCT」を作り直すので、
必要になった時に TbInit を実行するのであれば、
WHERE TbInit() 部分は削除して・・・・っていう使い方自体は変わらない事を言いたかったです。

では、TbInit / IdJoin について、#3と同じものを以下に再掲載します。
(関数名は#2と同じですが、中の記述はどちらも変更しています

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

この回答への補足

>30246kikuさん

下記の「お礼入力」で記載いたしましたが、
テーブルできました!!!

何を勘違いしていたのか…いろいろとゴチャゴチャにしてました…
本当に申し訳ございませんでしたm(__)m

テーブルは完成したのですが、
クエリでエラーが…
「複数値を持つフィールドを使用した操作は無効」と出てしまいます!
どうすればよいでしょうか?
DJoinの方も試したのですが、「式に未定義関数'DJoin'がある」と出てきてしまいます…定義の方法がわかりません…

本当に知識がなく、申し訳ないです…

更にはテーブルを作成する標準モジュールをイベント発生にするにはどうすればよいでしょうか?
「Private Sub」で作ればよいのでしょうか?

本当にいろいろご迷惑ばかりお掛けし、大変申し訳ございませんm(__)m

何卒、何卒、ご教示お願い申し上げますm(_ _)m

補足日時:2012/09/06 20:33
    • good
    • 0
この回答へのお礼

>30246kikuさん

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

ご丁寧にご説明を頂き、本当に感謝しております!

>For Each v In TbHead
部分の「TbHead」で、「変数が定義されてません」とエラーがでてしまいます…
どうすればよいでしょうか?

何度も何度も大変申し訳ございません…

ご教示頂きながらも必死に内容を理解しようと頑張ってはいますが、独学にてほんの少しだけ理解しているだけの状態で、こんな無謀なことをしようとする時点で間違っていますよね…

お忙しいところ、大変ご面倒をお掛けいたしますが、
もう少しだけお付き合い頂けませんでしょうか?

引き続き、何卒 よろしくお願い申し上げますm(_ _;)m

お礼日時:2012/09/06 19:40

#7です



まだ閉じられていなかったので・・・・


ごめんなさい。
#7の最終部分に記述していたコメントについては、VBA 記述内での話です。
そう伝わっていれば、良いのですが・・・

実現したいことと、VBA記述が一致している/いないは抜きにして
(質問の解釈違い等もあるので・・・)

VBA を記述・提示する・・・・記述・処理を読んで理解してほしい・・・
私はこれを1番に考えます。
初心者であろうが XXXX を実現したい・・・VBAが必要そう・・・VBAを提示
(初心者用実現方法集・・・みたいな便利なものがあれば良いと思いますが)
(XXXX を実現したい・・・・初心者云々の区別はないと思いますけど)
コメントを付けて、それを読まれ、理解したつもり・・・・になってもらっては困る。
(実際には、私は困りませんが)
また、コメントはこう付けるものだ・・・と思ってもらっても困る。

私はそう思って、VBAの部分と、説明する部分は分けて回答してます。
これが現状のスタイルになっています。


今回 GetString の話が出てきたので、それを使って回答した例として

Accessのデータをテキストファイルで出力する方法を教えてください。
http://oshiete.goo.ne.jp/qa/6003762.html
ここでも、VBAの記述と、説明部分は分けてます。
説明が足りなかったかと思った事もありましたが、処理を読まれたんでしょうか??


なぜそう記述したのか・・・・これに該当する回答例は

Accessでチェックボックス選択でレコード追加
http://oshiete.goo.ne.jp/qa/7451970.html
の#5にある
  Me.天候 = Me.天候   '編集状態にしたいため
部分になるでしょうか。


複数のイベントが絡み合う・・・・
この場合も、VBA記述を読んで理解してほしかったので説明部分は分けてました。

コンボボックス 選択後のカーソル制御
http://oshiete.goo.ne.jp/qa/6966456.html
ちなみに、このQAでのVBAの記述・書き方、どれがお好みですか?



何を持って上達したか・・・・今のところ曖昧状態になってます。

解決されたらOKとみるのか・・・
ベストアンサーにならなかったらNGとみるのか・・・・
(解決されたのなら誰がベストアンサーになっても良いと思ってますけど・・・)
補足が無ければOKとみるのか・・・
質問者さんの反応が何もなければNGとみるのか・・・

基本、補足等あれば追加回答していますが、xx 回以上の時はNGとみるのか・・・
解決に向けて頑張っている方を見放すことはしていなかったと思います。
(私がギブアップしたのなら別ですけど)
それだけでもOKとみるのか・・・

検討課題、ありがとうございました。
いろいろと考えてみます。


なお、一連の回答での記述(VBA、クエリ)の仕方等々、ほんの一例にしかなりません。

以上 頑張ってください
(一応解決されたようですので、これを最後にします)
    • good
    • 0
この回答へのお礼

>30246kikuさん

ご返答ありがとうございました。

>まだ閉じられていなかったので・・・・
何かご意見があればと念のため閉じておりませんでした。

>そう伝わっていれば、良いのですが・・・
どちらとも取れたので、コメントさせて頂きました。

>VBA を記述・提示する・・・・記述・処理を読んで理解してほしい・・・
>私はこれを1番に考えます。
これはご最もだと私も思います!

>(初心者用実現方法集・・・みたいな便利なものがあれば良いと思いますが)
これいいですね☆
>コメントを付けて、それを読まれ、理解したつもり・・・・になってもらっては困る。
>また、コメントはこう付けるものだ・・・と思ってもらっても困る。
できる方たちにとっては、自分で理解してから納得してと言うのも当然!
確かにそう思います!ですが、これは人それぞれかと…
今後のことも考え、必死に理解しようとする人と今だけ解決できればと言う人は存在しますから…
私は前者です!
>また、コメントはこう付けるものだ・・・と思ってもらっても困る。
これはあまり思う方は少ないと感じます。
ただコメントと説明に関して、本当に初心者は読んでもチンプンカンプンです…
全くの初心者(誰に教えて貰うことのできない環境)は、それを理解しようと考え、本読んだり、調べたりして、
少しづつ少しづつ理解をしていくんです。(しかも本業ではないところで必要な場合が多いので…)

いくつも処理のご提示ありがとうございます!
とても参考になります!

>説明が足りなかったかと思った事もありましたが、処理を読まれたんでしょうか??
いえ!足りないなんてとんでもない!
処理は読んだというよりは、ひとつひとつ試して、この処理はこう動くんだなとか
ここの部分はこういう意味があるんだとか理解するようにしてます。

>ちなみに、このQAでのVBAの記述・書き方、どれがお好みですか?
どれもわかり易いですが、あえて言うのであれば、2番目ですかね。
初心者に対してわかり易くという点で考えると、最初にこういう風に動かすというある程度の説明があり、
VBA記述 最後に補足と違った処理をする場合は~の例をといった感じがわかりやすですかね。

沢山の方々が言うのですが、過去記事は見ましたか?ということ。これは初心者にとっては見たとしか言えません。
え?見てないじゃんって思うかと思いますが、まず初心者は処理の言い方がわかりません!なので、検索方法(検索クエリのキーワード)が乏しいのです…
だから自分でわかる範囲での、キーワードでしか検索できていないのですね…


>何を持って上達したか・・・・今のところ曖昧状態になってます。
私の中では、解決したらOKだと思います。もちろん質問者さんが感謝してくれたらの話です。
なので、回答に対してありがとうと言われればいいのだと感じます。


>検討課題、ありがとうございました。
>いろいろと考えてみます。
こんな私に丁寧にご教示を頂いた上に、初心者の戯言にもお付き合い頂いて、
本当にありがとうございました!


>なお、一連の回答での記述(VBA、クエリ)の仕方等々、ほんの一例にしかなりません。
これが奥が深く、私はまだまだと感じる点ですね…

解決できましたし、30246kikuさんのお蔭で、もう少しで完成になりそうです☆

頑張ってVBAを少しでも理解できるように努力していきます!
また何かあれば、その際はまたご教示の程 よろしくお願い申し上げますm(__)m

本当に心より感謝しております。
ありがとうございました!!!

お礼日時:2012/09/10 17:29

#6です



動きましたか・・・説明不足もあってどうかと思っていましたが・・・
(VBA記述の処理については、ほとんど触れていなかったですね)
(理解されてから使ってみてください)

(1)
ワークテーブル「TCTT」の内容ですが、MkTable を実行するまで書き変わることはありません。
実行のタイミングで、TbHead に記述されたテーブル、IDを参照して作り直します。
例えば「Tプラン」で、プランIDを追加/変更等行っても、「TCTT」内が変わることはありません。
「TCTT」を見たい時に、MkTable を実行してから見る・・・この手順が良いと思います。
「Tプラン」を変更したから・・・「T見積」を変更したから・・・
その都度、MkTable を実行・・・あまり意味が無いように思います。
というのは、「TCTT」の内容ではデータベースとしては使えない(使いにくい)内容です。
見るため・・・の用途に絞った方が良いのかと思います。
なお、「TCTT」名を変更したら、修正は MkTable 内の2か所だけです。

(2)
クロス集計クエリ内に、WHERE TbInit() の記述がある限り、その時点での情報が「TCT」に作成されるので、常に最新のものを見ることが出来ます。
WHERE TbInit() の記述を削除すると、「TCT」の内容は TbInit を実行するまで書き変わりません。
なので、「TCTT」同様自動で書き変わることはありません。

通常のクロス集計では中間のテーブル(今回の「TCT」)を作ることはしません。
当初、「T書類1」~「T書類4」の様な連番のテーブルは、1つのテーブルにして・・・
その事を1番言いたくて、「TCT」の構成を提示しました。
もし、1つのテーブルになったのなら TbInit 自体不要になるものです。

その構成になった場合、クロス集計が楽なのか・・・・
その部分だけを回答するのも何だから(1)についても書いておこうかと・・・
なので、テーブル名も「TC」のテンポラリ「TCT」、「TCT」は使ったから次は「TCTT」

(1)について記述しておいてよかったなぁ・・・と#1さんのお礼を見て思いました。
というのは、Excelで作って、どうやったら綺麗にインポートできるんだろうか・・・
こっちの方が方法を思いつきませんでした。

(2)について、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)

これは、抽出したレコードセットで、
・フィールド間の区切りを ""
・レコード間の区切りを "," (カンマ1文字で)
実際に抽出したフィールドは1つしかないので、"" 指定はあってもなくても
レコード間の区切りは必ず付加されるので、一番最後は不要だから Left で1文字削除したものにします。
ただ、"," (カンマ1文字)を指定していたので -1 なだけで、2文字指定していたら -2 です。
もし、この区切り文字を変数 sDel に持っていていたとしたら

    sDel = ","
    sS = rs.GetString(adClipString, , "", sDel)
    sS = Left(sS, Len(sS) - Len(sDel))

になると思います。
専用・汎用は、それなりに難しいですね。


※ 今回の反省

・#2で、(1)(2)用のVBAを記述し、標準モジュールに・・・と書いてました。
モジュールを分けて確認されていた・・・これ、わかりませんでした。

・#3で Private の関数を作ったので、一気にコピー&貼り付けできるようにVBAをまとめて書いていましたが、上記で分けた部分部分で処理されていたとは思いませんでした。
1つのモジュールに記述する・・・・説明不足でした

・テーブル名等が連番ではない事がわかった時点で、(2)のクロス集計について引っ込めれば良かったのかも
 (でも、動かないものではないので・・・・で、納得させてました)

・#2でテンポラリテーブルと表現していましたが、(意味的に)使い終わったら削除する・・・・これが強いのか・・・と思って#3以降は、ワークテーブルと表現を変えてました。(あまり意味無いのかもしれませんが)

いろいろいじってみてください。 失礼しました。


関数内の処理について説明が必要であれば、補足してください。

コメントについては、いろいろな考え方があると思います。
私はコメントをあまり書かない派です。
読めばわかることは、書きません。
何故そうやったか・・・理由はよく書きます。

処理を読む時、邪魔するコメントもあります。
(そんなの処理を読めばわかるよ・・・)
処理を変えたけど、古いままのコメントがあったりします。
(なにやってんだか・・・・)

コメントを読むのに慣れない方が良いと私は思います。
    • good
    • 0
この回答へのお礼

>30246kikuさん

返信が遅れ、大変申し訳ございませんでした。

ご丁寧にご回答ありがとうございます!

疑問点が解決できました!
本当にありがとうございましたm(__)m

本当に丁寧に教えて下さり、感謝感激です!(涙)

30246kikuさんの反省点についてですが、
まず私のような初心者がこんなにも難しいことをやろうとしている時点で間違っておりますよね…


コメントについてですが、
あくまで私個人の見解を述べさせて頂きます。
まず、このようなQ&Aサイトに質問をする人には2種類いると考えます。

1つ目は知識が乏しく、仕組みなどをあまり理解していないケース。
こちらは仕事上でどうしても必要なのだが、理解しようにも理解ができない状態。
業務上で依頼されたものを作成するために質問をしているので、自分自身が必要な情報を全て得たいと考えている。

2つ目はある程度の知識を持っているが、解決できないものがあったので、質問するケース。
こちらは仕事上などはあまり関係なく、少しのヒント(違った見解)が貰えればそれでよいと考えている。

ちなみに私は前者です…

言い訳をするというのであれば、他の前者の方々もそうだと思いますが、
業務上でどうしても必要なのに、何をやればいいかすらわからない(知識がない為)、
でも絶対にやらなければならず、「できません」があり得ない。しかも納期が迫っている…
そうなった場合、何が何でも解決しようとするものの、今から勉強して作り上げるのはほぼ不可能な為、
知識のある方々に教えを乞う。「全て教えて下さい!少しヒントを貰ったとしても解決しようがないから…」となるわけです…


生意気なことを言うようですが、上達という観点でみれば、全く知識のない方にもわかるように説明ができなければならないと考えます。
知識や技術だけを養っていても、新しいものはどんどんを出てきますし、追いついていけなくなるのが実情だと思います。(若い世代も出てきます)
だったらSEなどの人をまとめ上げることを目指していかなければと感じます。

そのようにならなければ、知識だけある頭でっかちのただのオタクになってしまいますよね?


30246kikuさんはコメントをあまり書かない派とおっしゃっておりましたが、それでは勿体ないと思います。
そう感じたのは、私のような知識がない人にここまで丁寧にご教示頂けたからです。

上記を踏まえ、検討をしてみて下さい。
前者の場合はコメントも含め丁寧に教えてあげる。
後者の場合はいくつかの見解を示すだけ。

そうすれば私以外にも同じように悩んでいる方々にも助けにもなりますし、
少しでも上達をと考えられている30246kikuさんにも良いかと思います。


生意気な意見をしたので、
もしご気分を悪くされたのであれば、大変申し訳ございません。
ただの初心者の戯言だと思って流して下さい。


今回、とても細かくご丁寧にご教示を頂きまして、
本当に感謝しております!
本当に本当にありがとうございましたm(__)m

お礼日時:2012/09/10 11:13

#5です



やっぱり説明は苦手ですね。
少しでも上達したいと思って回答するようになったのですが・・・

> クエリでエラーが…
> 「複数値を持つフィールドを使用した操作は無効」と出てしまいます!

これについて、全くわかりません。また、推測できません。


お手数ですが、以下手順で確認していただけませんか。

・新規mdb or accdb を作成します
・テーブル「T顧客」「T見積」「T請求」「Tプラン」「T最終」をインポートします。
 (インポート元は、今までいじっていたもので良いと思います)
・ワークテーブル「TCTT」を作成します。

「an」(オートナンバ:主キー)
「顧客ID」「名前」「見積ID」「請求ID」「プランID」「最終ID」
「名前」以外は数値型:長整数
「an」以外は、値要求:いいえ としておきます。

・ワークテーブル「TCT」を作成します。

「顧客ID」「種類」「WorkID」の3つ
「顧客ID」「WorkID」は数値型:長整数
「種類」はテキスト型

・VBEの画面を表示します。
・ ADO を使えるように、参照設定で Microsoft ActiveX Data Objects x.y Library 追加
(私の確認時、x.y は 2.5 を選んでました)
・挿入で標準モジュールを選択し、そこに#3の VBA 全部を転記(コピー&貼り付けで)
・VBAの MkTable を実行し、「TCTT」内がデータで埋まるか確認
・VBAの TbInit を実行し、「TCT」が出来上がるか確認
・クエリのデザイン(SQLビュー)で、#3と同じ以下を転記(コピー&貼り付けで)

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'がある」と出てきてしまいます…
> 定義の方法がわかりません…

#1さんがURL記述されていますよね。
DJoin を使用する場合、
そこの記事に書かれている DJoin の処理・記述を組み込まなくてはなりません。
簡単には、標準モジュールにコピー&貼り付けすれば使えると思います。


申し訳ありませんが、今までの操作は一旦忘れて頂いて、上記順で確認頂けませんか。

以上 よろしくお願いいたします。


> 更にはテーブルを作成する標準モジュールをイベント発生にするにはどうすればよいでしょうか?
については、どう解釈してよいのか迷ってますが、
ボタンがクリックされた時に、MkTable を実行という事で良いですか。であれば以下で

Private Sub ボタン_Click()
  Call MkTable
End Sub
    • good
    • 0
この回答へのお礼

>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

お礼日時:2012/09/07 11:23

#3です



時間がたっていたので、解決されていたでしょうか。

> ですが、(2)のワークテーブル「TCT」作成の段階
> >TbInit() / IdJoin の使用方法は以前の通りです。
> と頂きましたが、
> >sSql = "INSERT INTO TCT(顧客ID, 種類, 書類ID) " _
> >& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"」
> の部分はどのように記載すれば宜しいでしょうか?

上記の sSql 作成部分のものは、#2での「書類1ID」~「書類4ID」の時のものです。

#3では、「TCT」の構成も変更しています。
この辺は、#3を再度見てください。
特に、
> >TbInit() / IdJoin の使用方法は以前の通りです。
と書いてましたが、使い方は一緒ですが、記述を変更しています。
#2、#3見比べて頂ければと・・・・・

#2での記述は捨てて頂いて、#3でやってみてください。

なお、関数名とかワークテーブル名とか、適宜変更してください。
    • good
    • 0
この回答へのお礼

>30246kikuさん

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

ずっと悩み続けております…(ToT;)

>上記の sSql 作成部分のものは、#2での「書類1ID」~「書類4ID」の時
>のものです。
ご教示頂いた内容で変更しております。。。

>sSql = "INSERT INTO TCT(顧客ID, 種類, WorkID) " _
>& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"←この部分はTCxの書類xIDを「For i = 1 To 4」でx値1~4まで持ってくるという認識で間違いないでしょうか?

だとすれば、この部分の記述を変更すればと思い、いろいろ試しましたが上手くいかず…知識が乏しくてお恥ずかしいです…

T見積,見積ID、T請求, 請求ID、Tプラン,プランID、T最終,最終IDと全てのテーブルとID名が違う時の記述がどうしてもわからずです…

お忙しいところ、大変ご面倒をお掛けいたしますが、ご教示頂けませんでしょうか?

何卒、よろしくお願い申し上げますm(_ _)m

お礼日時:2012/09/06 17:57

#2です



おっと、補足の通知があったのね・・・で、その内容も含めてみました。
テーブル名等、連番じゃなかったんですね。

では、まず標準モジュールに記述する内容全てを以下に(参考程度にしてください)
拙い説明は後で


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名に重複がある場合にはこの方法は使えません)

(1)を実現する為のワークテーブル「TCTT」項目名変更

「an」(オートナンバ:主キー)
「顧客ID」「名前」「見積ID」「請求ID」「プランID」「最終ID」
「名前」以外は数値型:長整数
「an」以外は、値要求:いいえ としておきます。

処理するテーブルが増えた場合、
・ここのテーブルのフィールドを追加する
・関数 TbHead 内を修正する
この2つを忘れずに行ってください。

そして、MkTable を実行します。

(2)ワークテーブル「TCT」を変更します。

構成を「顧客ID」「種類」「WorkID」の3つに変更します。
「顧客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顧客;

(2)については、DJoin が楽だと思います。

※ 内容を理解した上で、責任もって・・・・
 (わからない部分については、補足いただければ・・・・わかる範囲で回答可)

ただ、(1)の様な使い方はあまりやりませんかね
(やってできないことはない・・・・っていうレベルのものかも)
「Access 複数テーブルをまとめる」の回答画像3
    • good
    • 0
この回答へのお礼

>30246kikuさん

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

(1)出来ました!感激です!!!

拙い説明なんてとんでもない!わかりやすくて大変参考になります!


ですが、(2)のワークテーブル「TCT」作成の段階
>TbInit() / IdJoin の使用方法は以前の通りです。
と頂きましたが、
>sSql = "INSERT INTO TCT(顧客ID, 種類, 書類ID) " _
>& "SELECT 顧客ID, " & i & ", 書類" & i & "ID FROM TC" & i & ";"」
の部分はどのように記載すれば宜しいでしょうか?

知識不足で大変申し訳ございませんm(__)m
引き続きのご教示をよろしくお願い申し上げます。

お礼日時:2012/09/06 10:18

では、ゴリゴリ記述してみます。

(以下参考まで)

確認した時の環境は、
テーブル「TC」 (「T顧客」と同じ)
テーブル「TC1」~「TC4」 (「T書類1」~「T書類4」と同じ)


(1)の場合、テンポラリテーブル「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」を表示しないようにするとか・・・


(2)#1さん紹介の方法が簡単かと思いますが、違う方法でやってみます。

クロス集計を使ってみようか・・・というもの(こっちの方がちょっと複雑です)
テンポラリテーブル「TCT」を使用します。
構成は「顧客ID」「種類」「書類ID」の3つ(いずれも数値型:長整数)
「顧客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 の値になるようにします。
(「T書類1」~「T書類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","T書類1","顧客ID = " & 顧客ID) AS 書類1ID
, DJoin("書類2ID","T書類2","顧客ID = " & 顧客ID) AS 書類2ID
, DJoin("書類3ID","T書類3","顧客ID = " & 顧客ID) AS 書類3ID
, DJoin("書類4ID","T書類4","顧客ID = " & 顧客ID) AS 書類4ID
FROM T顧客;


いずれにせよ、何をやっているのかを理解された上で、使用された方が良いと思います。


なお、
> その他に複数同じような構造のテーブルがあります。
という事なので、For i = 1 To 4 の 4 を変更していく等、微調整は必要です。
「Access 複数テーブルをまとめる」の回答画像2

この回答への補足

申し訳ございません…

更に「T顧客」以外のテーブルは「顧客参照ID」となっていて、「T顧客」からルックアップで値を持ってきております…テキスト型ではなく「数値型:長整数」

T顧客…顧客ID(主キー)、名前、…
T見積…顧客参照ID(重複キー有、数値型で表示はテキスト)、見積ID、…
T請求…顧客参照ID(重複キー有、数値型で表示はテキスト)、請求ID、…
Tプラン…顧客参照ID(重複キー有、数値型で表示はテキスト)、プランID、…
T最終…顧客参照ID(重複キー有、数値型で表示はテキスト)、最終ID、…

初期の作成過程で本を見ながら作成していたので、後のことをあまり考えずに作成しておりました…

ご教示をお願い申し上げますm(__)m

補足日時:2012/09/05 17:56
    • good
    • 0
この回答へのお礼

>30246kikuさん

ご回答ありがとうございました!

素晴らしいです!まさに添付図のようにしたかったのです!!!


しかし…今ご教示頂いたもので作成しようとしていたのですが、
本来、私が作成している内容は、

T顧客…顧客ID(主キー)、名前、…
T見積…顧客ID(重複キー有)、見積ID、…
T請求…顧客ID(重複キー有)、請求ID、…
Tプラン…顧客ID(重複キー有)、プランID、…
T最終…顧客ID(重複キー有)、最終ID、…
 ・
 ・
 ・
となっているのです…
質問事項でわかり易いようにと思い、簡素にして質問してしまいました。
やりたいことはまさにご教示頂いたまんまなのですが、この場合ですとどのように記述すればよいのかがわかりません…

大変ご面倒をお掛けし、申し訳ありませんが
今一度、ご教示の程 よろしくお願い申し上げますm(__)m

お礼日時:2012/09/05 16:44

>(1)一つのテーブルかクエリで抽出


相当に面倒だと思います。VBAでゴリゴリとテーブルに書き込んでゆくしか無いような?
妥協案として、レポートでなら重複レコード非表示に設定すれば近いことは出来そうです。
http://www.sanryu.net/acc/tips/tips236.htm

>(2)更にまとめたものにしたいです。
こちらも厄介です。
YU-TANG さんと Gin_II さんが
http://www.f3.dion.ne.jp/~element/msaccess/AcTip …
http://www.f3.dion.ne.jp/~element/msaccess/DJoin …
DJoin関数を提供されていますが意味を理解しアレンジするのはかなり難しいかと思います。
    • good
    • 0
この回答へのお礼

>nicotinismさん

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

難しいのですね…
アクセスが無理であれば、エクセルで一度データを吐き出して、細々と関数を使って整理していき、最終的なデータになれば、エクセルからアクセスに戻せば…?

なんてことを考えておりますが、そんなことできますかね???

非常に苦戦しております…お知恵をお貸し下さい…

よろしくお願い申し上げますm(__)m

お礼日時:2012/09/05 15:45

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!