MS Access Tips/Sample and VBA and Blog customize etc...

グループ化したレコードを横に展開する

下記のテーブルを、

Tbl1
IDフィールド1フィールド2
1 A
2 B
3 C
4 D
5 E
6 F

下記のように表示したい。

RptHorizontalSpread2.gif

というような質問がありました。フィールド1でグループ化して、詳細を横3列で改段する、という仕様になりますね。

難易度:

方法としては、以前の記事「レコードを横に展開する」の応用で比較的簡単に実現できます。

Tbl1をレコードソースとするレポートを作成します。

「並べ替え/グループ化の設定」でフィールド1でグループ化して、グループヘッダーを表示させます。

ページヘッダーにラベルで、項目名「フィールド1」「フィールド2」を配置します。

グループヘッダーに下記のテキストボックスを配置します。

プロパティ設定値
コントロールソース=Count(*)
名前GCnt

グループヘッダーの可視は「いいえ」にします。

詳細セクションに、フィールド1、フィールド2をテキストボックスとして配置します。

さらに、テキストボックスを配置して、下記のように設定します。

プロパティ設定値
コントロールソース=1
集計実行グループ全体
名前GSeq
可視いいえ

GCntはグループ内件数、GSeq はグループ内連番を取得します。

フォームモジュールを下記のように設定します。

2010/12/01 コード一部修正しました。

サンプルファイル が下記からダウンロードできます。
RptHorizontalSpread2_07.zip (Access 2007 形式 - 27kb)
RptHorizontalSpread2.zip (Access 2002-2003 形式 - 23kb)
RptHorizontalSpread2_2k.zip (Access 2000 形式 - 25kb)

2014/05/07追記: サンプルファイルに設定ミスがあったので修正したものに差し替えました。

サンプルには、比較のために、クロス集計クエリを利用した方法、と、サブレポートを使った方法、のサンプルも入ってますので、ご参考にしてください。

拍手する

14 Comments

mrn says..."グループが複数ある場合"

こんにちは。
こちらの記事を参考にさせていただき、サンプルMDBのクロス集計クエリを使用した方法で横3列展開するところまではできたのですが、グループを2つにすると(フィールド1にグループA、フィールド2にグループB、フィールド3に展開したいレコード内容)、歯抜けのような感じでレコードが飛び飛びになってしまいます。グループを複数作成した状態でレコードを横展開するにはどうすれよいでしょうか?

ちなみにレポートは印刷ではなく、ExcelかHTMLに出力しようと考えています。VBA使用の方法だとExcel出力時に横展開できなかったためクロス集計パターンで作成しました。

よろしくお願いします。

2012.08.19 23:57 | URL | #uxYHNhu6 [edit]
hatena says..."re:グループが複数ある場合"

クロス集計クエリの出力結果はうまくいっているのですか。

それともクロス集計クエリの段階で希望とは異なるのですか。

もし、クロス集計クエリの結果は希望通りなら、そのクエリを直接エクスポートすればいいかと思いますが、それではダメですか。

2012.08.20 18:55 | URL | #5uE6dEgY [edit]
mrn says..."re:グループが複数ある場合"

ご返信ありがとうございます。
クロス集計クエリの段階で飛び飛びになってしまっています。

あ 1 A
あ 1 B
あ 1 C
あ 2 D
い 1 E
い 2 F

このようなテーブルで、[あ、い]でグループ化し
更に[1、2]でグループ化したものを

あ 1 A B C
あ 2 D
い 1 E
い 2 F

のように表示したいのですが、クエリの結果は

あ 1 A B C
あ 2 D
い 1 E
い 2  F

のように、ずれてしまいます。2つ目のグループ[1、2]が
列番号に反映されていないのではと思うのですが
何か良い解決策はないでしょうか?

2012.08.20 23:23 | URL | #uxYHNhu6 [edit]
hatena says..."re:グループが複数ある場合"

現在のテーブル名とフィールド名が下記のようだとして、

T1
 F1 テキスト型
 F2 数値型
 F3 テキスト型



下記のように設定したらどうでしょうか。

行見出し
行: (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND F3 <='" & [F3] & "'")-1)\3

列見出し
列: (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND F3 <='" & [F3] & "'")+2) Mod 3


SQLなら、

TRANSFORM First(F3) AS F3の先頭
SELECT F1, F2, (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND F3 <='" & [F3] & "'")-1)\3 AS 行
FROM T1
GROUP BY F1, F2, (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND F3 <='" & [F3] & "'")-1)\3
PIVOT (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND F3 <='" & [F3] & "'")+2) Mod 3;

2012.08.21 14:13 | URL | #5uE6dEgY [edit]
mrn says..."re:グループが複数ある場合"

返信ありがとうございます。

記述いただいたSQLでほぼ求めていた形になりました。
ですが、同じグループ内にF3の内容が重複したレコードがあると
そこだけ空白になってしまうようです。

AND F3 <='" & [F3] & "'

の辺りが原因かと思い、色々試してみたのですがうまくいきませんでした。
度々で申し訳ありませんがお知恵をお貸しいただければ幸いです。

2012.09.02 21:24 | URL | #uxYHNhu6 [edit]
hatena says..."re:グループが複数ある場合"

すべてのフィールドが重複したレコードがあると、Access君はそれを見分けられないので、連番をつけることができません。

テーブルに、重複のない主キーフィールドはありませんか。
ないなら、オートナンバー型のフィールドを追加するなどして、主キーフィールドとしてください。

F3フィールドの代わりにその主キーフィールドを使って連番を演算します。
主キーフィールドを ID として

行見出し
行: (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND ID <=" & [ID])-1)\3

列見出し
列: (DCount("*","T1","F1='" & [F1] & "' AND F2=" & [F2] & " AND ID <='" & [ID])+2) Mod 3

2012.09.03 00:18 | URL | #5uE6dEgY [edit]
mrn says..."re:グループが複数ある場合"

早速の返信ありがとうございます。

F3をIDに変更したところ、重複レコードも表示されるようになりました!

感謝感激です。何度もお付き合いいただき、ありがとうございました。

2012.09.03 01:00 | URL | #uxYHNhu6 [edit]
ゆっぴ says..."1列目が表示されません"

いつも大変参考にさせていただいています。
こちらを参考にレポートを作成しましたが、1列目が表示されません。
表示したい列を3列に設定し、1列めの開始位置はテキストボックス配置位置にしています。
列の中身が1列分のみの場合は1列目の正しい位置に正しく表示されますが、複数列の場合1列目の内容が表示されず2列目から2列目の内容が表示されます。
どこを確認すればよいのか皆目見当がつきません。

あ A
あ B
あ C
い A
う B
う C



あ A B C
い A
う B C

としたいのですが

あ   B C
い A
う   C

となってしまいます。
よろしくお願いいたします。

2014.05.24 17:50 | URL | #- [edit]
hatena says..."re:1列目が表示されません"

この記事の説明通りにすればできるはずですので、
どこかの設定が間違っいてるでしょう。

グループ化の設定
テキストボックスの設定
モジュールのコード
などに記事と違いがないか確認して下さい。

それでも分からないようでしたら、このページの上部のメニューからBBSをクリックして、そこで再質問してください。

その際にファイルをZIP圧縮してアップロードしてください。
ファイルは現状のファイルのコピーを作成して、
質問に関係のあるオブジェクトのみ残したものに
してアップロードしてください。
レコードソースのテーブル、クエリ
レポート

2014.05.24 18:11 | URL | #5uE6dEgY [edit]
ゆっぴ says..."re:1列目が表示されません"

早速ありがとうございます。
職場で使用しておりますので、明日出社しましたらもう一度確認してみます。

2014.05.25 20:42 | URL | #- [edit]
ゆっぴ says...""

こんばんは。
まずサンプルで確認しようと思い、RptHorizontalSpread2にデータを追加してみました。

1 あ A
2 あ B
3 あ C
4 あ D
5 い E
6 い F
7 う G
8 う H
9 う I
10 え J
11 お K

すると

あ A B C
あ D

まではきちんと表示されますが、

い の行と う、え の行と お の行がそれぞれ重なってしまうのですが。。。
何か使い方が間違ってるのでしょうか。

2014.05.26 21:36 | URL | #- [edit]
hatena says..."re:サンプルのバグ"

設定ミスでした。

> い の行と う、え の行と お の行がそれぞれ重なってしまうのですが。。。
> 何か使い方が間違ってるのでしょうか。

グループヘッダーのテキストボックス「GCnt」の「集計実行」が「グループ全体」になってますが、それを「しない」に変更してください。
これで正常に表示されます。

2014.05.26 22:21 | URL | #5uE6dEgY [edit]
ゆっぴ says..."re:1列目が表示されません"

ありがとうございます。
明日早速確認してみます。
サブレポートを使用した方法で一応ほしいものができたのですが、レイアウトを修正する可能性があるので、少しでも簡単なほうがよいと思っています。

2014.05.27 21:58 | URL | #- [edit]
ゆっぴ says..."re:1列目が表示されません"

サンプルmdbは設定を修正してテストしましたらきちんと表示されました。
修正の内容は理解できたのでもっと考えてみればよかったです。

そこで、テーブルを使いたいものに変更しても問題なくできました。
やはりどこかミスがあったようです。
この後グループが複数あるものも作らないといけないので、mrnさんの場合を参考にがんばってみます。
ありがとうございました。

2014.05.29 11:54 | URL | #- [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/105-4c496926
該当の記事は見つかりませんでした。