アクセスでデータを横に並べる方法

アクセス2003を使って注文管理のデータベースを作っています。
テーブル「納品データ」のデータを発注番号ごとに納品日と納品数を横に並べたものを作りたいのですが、集計クエリでやってみてもうまくできませんでした。過去の質問の中にも似たようなものがあったのですが、一部理解できない箇所があったりして方法が分からなかったため、質問させていただきます。

■テーブル「納品データ」

｜納品番号｜発注番号｜納品日｜納品数｜
　　１　　　　　１　　　04/01　　５
　　２　　　　　１　　　04/02　　４
　　３　　　　　１　　　04/03　　３
　　４　　　　　２　　　04/06　　８
　　５　　　　　２　　　04/08　　７
　　６　　　　　３　　　04/04　　６
　　７　　　　　３　　　04/07　　５
　　８　　　　　３　　　04/09　　４
　　９　　　　　４　　　04/05　　７
　　10　　　　　４　　　04/08　　８


■今やろうとしているもの

発注番号｜納品日｜納品数｜納品日｜納品数・・・
　１　　　04/01　　５　　04/02　　４　　04/03　３
　２　　　04/03　　８　　04/04　　７　　
　３　　　04/02　　６　　04/03　　５　　04/04　４
　４　　　04/02　　７　　04/03　　８

うまく表形式でできなくて、ズレていてすみません。
最終的には、これをサブクエリとしてメイン－サブの親子レポートを作成しようと思っています。

No.3 ベストアンサー 回答者： nicotinism 回答日時： 2006/04/28 00:18

今回の場合、

クロス集計でなんとかなりそうと思いました。
その為には並べ替えに使用するキーが「一意」である必要があります
それには、発注番号 & 納品番号が使えそう・・・
そこで、発注番号「毎」 かつ 納品番号順での連番を作成することにします

DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号)
ここでTBL1の中から、tbl1.発注番号=" & 発注番号であり
かつ
TBL1.納品番号<=" & 納品番号であるものをカウントします。
（このクエリの現レコードの納品番号 以下のものを テーブルTBL1の納品番号から求めます）

なお、今回の場合は、年月日と納品数の二つを表示しなくてはいけませんので
選択クエリを二つ作成してUnion Allし、これをQQ（サブクエリ）としています

Format・・・については
カウントされた数は1,2、・・10,11・・となりますが、並べ替えの際に文字列として判断されると
並び順は1,10,11、・・2,20、となってしまいます。
また
Format（・・） & "1"、Format（・・） & "2" として年月日→数の並び替えを調整して
いますので、この為にも"00"として桁合わせを行っています。

余計に分からなくなったかも？と危惧しています。
説明は苦手なので、後はヘルプや書籍などで勉強してくださいませ。

この回答への補足

ご回答ありがとうございます。いろいろとアドバイスいただいたおかげで、だんだん分かってきました。できましたら、最後の最後にもう1つだけm(_ _)m 解説の解説をお願いできればと・・。

>ここでTBL1の中から、tbl1.発注番号=" & 発注番号であり
かつ
TBL1.納品番号<=" & 納品番号であるものをカウントします。

tbl1.発注番号=" & 発注番号　のところですが、具体的に何と何が同じであればということなのでしょうか・・。
tbl1.発注番号は、架空のもので、" & 発注番号はテーブル「納品データ」の発注番号のことを指しているのでしょうか？
同じように、TBL1.納品番号<=" & 納品番号のところも解説いただけると助かります。

理解力が足りなくて、ほんとうに申し訳ありませんがよろしくお願いします。

補足日時：2006/04/28 10:13

No.4 回答者： nicotinism 回答日時： 2006/04/28 23:14

DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号)

DCount の説明はヘルプでご覧になりましたか？理解されましたか？
今回の場合、
クエリの元となっているフィールド（TBL１.納品番号）、参照先のフィールド（納品番号）とも
同じなので分かりづらいかとは思いますが
DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号)
↓
DCount("*","TBL2","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号)
としたらこの意味は分かりますか？


冒頭の文は、同様にして
DCount("*","TBL1"（検索対象のテーブル名）,"TBL1.納品番号（このクエリのフィールド）<=" & 納品番号（検索対象のテーブルのフィールド名) & "and tbl1.発注番号（このクエリのフィールド名）=" & 発注番号（検索対象のテーブルのフィールド名))
という意味です。
DCount("*","TBL1"（検索 ～～号（検索対象のテーブルのフィールド名)) で一行です。

つまり、前回レスの
ここでTBL1の中から、tbl1.発注番号=" & 発注番号であり
かつ
TBL1.納品番号<=" & 納品番号であるものをカウントします。
（このクエリの現レコードの納品番号 以下のものを テーブルTBL1の納品番号から求めます）
となります。

では。

この回答へのお礼

ご回答ありがとうございました。丁寧にアドバイスしていただき、本当に感謝しています。まだまだ勉強不足で理解しきれていない部分もありますが、ヘルプや参考書などももっと活用してしっかりと応用できるようにしていきたいと思います。

お礼日時：2006/05/02 15:45

No.2 回答者： nicotinism 回答日時： 2006/04/27 07:53

それなら始めから・・・ (^_^;)

第二弾です。
（１）と（２）と（３）は消してからお試しください。
また（１）と（２）の間のSQL文だけ、（１）と（３）の間のSQL文だけ、
をそれぞれ貼り付けたらどうなるかも
試してください。
何かが見えてくると思います。

TRANSFORM First(QQ.F1) AS F1の先頭
SELECT QQ.発注番号, Sum(QQ.納品数計) AS 納品数計
FROM [
（１）
SELECT TBL1.納品番号, TBL1.発注番号, TBL1.納品日 as F1 , TBL1.納品数 as 納品数計,
format(DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号),"00") & "1" AS cnt
FROM TBL1
（２）
union all
SELECT TBL1.納品番号, TBL1.発注番号, TBL1.納品数 as F1, Null as 納品数計,
format(DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号),"00") & "2"AS cnt
FROM TBL1
（３）
]. AS QQ
GROUP BY QQ.発注番号
PIVOT QQ.cnt;

ついでに
納品日と納品数が左寄せになっているのは、異なるデータ型（数値、日付）をUnionした為の型変換で
どうしようもありませんが（あるかも知れないが私は知らない）、
それぞれ、
TBL1.納品日 as F1 → format(TBL1.納品日,"@@@@@@@@@@@") as F1
TBL1.納品数 as F1 → format(TBL1.納品数,"@@@@@@@@@@@") as F1
などと、@ の数を最大桁数+1 位にしておけば、それっぽく見えます。

この回答への補足

ご回答ありがとうございます。最初からやり直しになってしまったようで、お手数かけてすみませんでした。

>また（１）と（２）の間のSQL文だけ、（１）と（３）の間のSQL文だけ、をそれぞれ貼り付けたらどうなるかも試してください。何かが見えてくると思います。
勉強不足ではっきりとは見えませんが(^_^;)　（１）のところで納品日、（２）のところで納品数を表示させているというのは分かりました。

もしよかったら、１箇所解説をお願いしたい部分があります。

format(DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号),"00") & "1" AS cnt

format関数で00書式にしてそれに1をくっつけて列名にしているのはなんとなく理解できたのですが、Dcount関数の中で何が行われているのかが分からなくて、教えていただけたらと思います。
Dcount関数には引数が3つあってとか書いてあったのですが、よく分からなくて・・。よろしくお願いします。

補足日時：2006/04/27 11:44

No.1 回答者： nicotinism 回答日時： 2006/04/26 13:30

こんな風でしょうか？

TRANSFORM First(QQ.F1)
SELECT QQ.発注番号
FROM [

SELECT TBL1.納品番号, TBL1.発注番号, TBL1.納品日 as F1 ,
format(DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号),"00") & "1" AS cnt
FROM TBL1

union all
SELECT TBL1.納品番号, TBL1.発注番号, TBL1.納品数 as F1,
format(DCount("*","TBL1","TBL1.納品番号<=" & 納品番号 & "and tbl1.発注番号=" & 発注番号),"00") & "2"AS cnt
FROM TBL1

]. AS QQ
GROUP BY QQ.発注番号
PIVOT QQ.cnt;

※クロス集計をレポートにしようとすると、フィールド数の増減があるので
苦労されると思います。

参考URL：http://www.ruriplus.com/msaccess/tch/tch_007.html

この回答への補足

ご回答ありがとうございます。そのままコピペしたらできました。ただ、レポート上で1行ずつの数量合計を出そうと思ったら、12と出したいところが５４３となってしまいました。合計を出すことはできるのでしょうか？

補足日時：2006/04/26 16:59