accessの集計について

質問

アクセスにデータを取り込んで、必要なデータを抽出するクエリ（集計_サブ[添付図１]）を作りました。
左から、店舗No・日付・商品情報・金額です。

それを元に、集計をして店舗別で、商品毎に月前半・後半と集計をしたいです。[添付図2]

クロス集計で出来そうな気がしますが、店舗別、商品別にして半月毎に集計する事は可能でしょうか？

もしくはvba使わないと不可能でしょうか？

回答No.18

chayamati

フォーム、レポートのデザインは数ステップで出来ますが
各フィールドのサイズ調整等をします
デザインビューでは各リボンの中のツール探すのが大変です
よく使うツールを右クリックで〈クリックアクセスツールバー〉
に追加,
次回からワン操作で可能
またツールバーを右クリックで〈クリックアクセスツールバー〉を
リボンの下に配置すると表示幅が広がります
自分の設定を添付します

回答No.17

chayamati

＞アクセスにデータを取り込んで、必要なデータを抽出するクエリ（集計_サブ[添付図１]）を作りました。
★今までこのテーブルが念頭になっかたので、このテーブルを〈T_情報〉してコーディング追加しました
　以下は全リストですが追加修正個所は最初の10行くらいです
添付の基準日を入力すると一気に〈T_売上棚〉の生成迄進みます
----------------------------------------------------------
Option Compare Database
Private Sub Form_Load()
DoCmd.SetWarnings True
DoCmd.RunSQL ("delete from T_売上 ; ")
DoCmd.RunSQL ("insert into T_売上(日付,店舗№,商品名,金額) select 日付,店舗№,商品名,金額 from T_情報 ;")
DoCmd.RunSQL ("update T_売上 set 主鍵=店舗№&商品名 ;")
基準日 = Date
日付RTN
End Sub
Private Sub 基準日_Exit(Cancel As Integer)
日付RTN
メインRTN
End Sub
Private Sub メインRTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("update T_売上 set 前半 = iif(day(日付)<=15,金額,0) ;")
DoCmd.RunSQL ("update T_売上 set 後半 = iif(day(日付)>15,金額,0) ;")
DoCmd.RunSQL ("DELETE from TMP_売上;")
DoCmd.RunSQL ("insert into TMP_売上(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初 and forms!メニュー!月末 GROUP BY 日付,店舗№,商品名,主鍵;")
DoCmd.RunSQL ("DELETE from T_売上棚;")
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初 and forms!メニュー!月末 GROUP BY 日付,店舗№,商品名,主鍵;")
前月取込RTN
DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額1,前半1,後半1,主鍵) select 店舗№,商品名,金額,前半,後半,主鍵 from TMP_前月; ")
DoCmd.OpenReport "R_売上棚", acViewPreview
End Sub
Private Sub 日付RTN()
月初 = 基準日 - Day(基準日) + 1
月末 = 月初 + 31 - Day(月初 + 31)
月末1 = 月初 - Day(月初)
月初1 = 月末1 - Day(月末1) + 1
End Sub
Private Sub 前月取込RTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from TMP_前月")
DoCmd.RunSQL ("insert into TMP_前月(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初1 and forms!メニュー!月末1 GROUP BY 日付,店舗№,商品名,主鍵;")
' DoCmd.RunSQL ("insert into TMP_売上(店舗№,商品名,金額,前半,後半) select 店舗№,商品名,金額,前半,後半 from TMP_前月;")
DoCmd.RunSQL ("uPdate TMP_前月 set 主鍵 = 店舗№ & 商品名 ;")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 金額1= TMP_前月.金額 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 前半1= TMP_前月.前半 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 後半1= TMP_前月.後半 ; ")
End Sub

Private Sub 閉じる_Click()
DoCmd.Close
DoCmd.Quit '終了の時
End Sub

回答No.16

chayamati

テーブルの一覧をリレーションシップの画像を添付します
元情報のテーブルを〈T_情報〉とし、〈T_売上〉〈TMP_売上〉〈TMP_前月〉を使って〈T_売上棚〉を生成し、
　〈T_売上棚〉をレコードソースとするレポート、フォームをします
1.フォームの作成手順
　〈T_売上棚を開く〉→〈作成リボン〉→〈その他のフォーム〉
　→〈複数のアイテム〉
2.レポートの作成手順
　〈T_売上棚を開く〉→〈作成リボン〉→〈レポート〉
----------------------------------------------

ご質問の画像を見直したとき試験に出そうと思いました
良い課題を頂きありがとうございます

回答No.15

chayamati

今晩は
ようやく完成しました。
処理結果を添付します。
①結果の確認のため商品名=当月を１２月、商品名=前月を１１月のレコードを登録加
②店番号=3、商品名=3◆◆のレコードを両月ともに登録
この処理のポイントは
主キー、〈フィールドのインデックスプロパティー〉=重複なしのレコードは
　最初のレコードのみ有効
以下が完了したコードリストです
尚、命令文の〈DoCmd.SetWarnings False〉を
〈DoCmd.SetWarnings True〉処理を続けるときはF5またはF8
続いてテーブルの一覧をリレーションシップで回答します
閉めるのをお待ちください。
---------------------------------------
Option Compare Database
Private Sub Form_Load()
基準日 = Date
日付RTN
End Sub
Private Sub 基準日_Exit(Cancel As Integer)
日付RTN
メインRTN
End Sub
Private Sub メインRTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("update T_売上 set 前半 = iif(day(日付)<=15,金額,0) ;")
DoCmd.RunSQL ("update T_売上 set 後半 = iif(day(日付)>15,金額,0) ;")
DoCmd.RunSQL ("DELETE from TMP_売上;")
DoCmd.RunSQL ("insert into TMP_売上(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初 and forms!メニュー!月末 GROUP BY 日付,店舗№,商品名,主鍵;")
DoCmd.RunSQL ("DELETE from T_売上棚;")
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初 and forms!メニュー!月末 GROUP BY 日付,店舗№,商品名,主鍵;")
前月取込RTN
DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額1,前半1,後半1,主鍵) select 店舗№,商品名,金額,前半,後半,主鍵 from TMP_前月; ")
DoCmd.OpenReport "R_売上棚", acViewPreview
End Sub
Private Sub 日付RTN()
月初 = 基準日 - Day(基準日) + 1
月末 = 月初 + 31 - Day(月初 + 31)
月末1 = 月初 - Day(月初)
月初1 = 月末1 - Day(月末1) + 1
End Sub
Private Sub 前月取込RTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from TMP_前月")
DoCmd.RunSQL ("insert into TMP_前月(店舗№,商品名,金額,前半,後半,主鍵) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半),主鍵 from T_売上 " _
& " where 日付 between forms!メニュー!月初1 and forms!メニュー!月末1 GROUP BY 日付,店舗№,商品名,主鍵;")
DoCmd.RunSQL ("uPdate TMP_前月 set 主鍵 = 店舗№ & 商品名 ;")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 金額1= TMP_前月.金額 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 前半1= TMP_前月.前半 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 後半1= TMP_前月.後半 ; ")
End Sub
Private Sub 閉じる_Click()
DoCmd.Close
' DoCmd.Quit '終了の時
End Sub
------------------------------

回答No.14

chayamati

〈T_売上棚〉の生成の確認ため〈R_売上棚〉のデザインビューです
レポートヘッダの〈=[Forms]![メニュー]![月初]〉書式　"前月対比表 "ggge\.m"月分"
〈=DATE()〉の書式　ge\.m\.d(aaa") 印刷"

回答No.13

chayamati

前月のみの処理対応は〈Private Sub 前月取込RTN()〉
終わりの命令文解決です
-----------------------------------------------------
Private Sub 前月取込RTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from TMP_前月")
DoCmd.RunSQL ("insert into TMP_前月(店舗№,商品名,金額,前半,後半) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半) from T_売上 " _
& " where 日付 between forms!メニュー!月初1 and forms!メニュー!月末1 GROUP BY 店舗№,商品名;")
DoCmd.RunSQL ("uPdate TMP_前月 set 主鍵 = 店舗№ & 商品名 ;")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 金額1= TMP_前月.金額 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 前半1= TMP_前月.前半 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 後半1= TMP_前月.後半 ; ")
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額1,前半1,後半1) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半) from T_売上 " _
& " where 日付 between forms!メニュー!月初1 and forms!メニュー!月末1 GROUP BY 店舗№,商品名;")
End Sub

回答No.12

chayamati

リレーションシップとメニューフォームの画像です
メニューフォームの非連続のテキストボックスの名前は左のラベルの標題
閉じるのラベルボックスの名前は標題と同じで閉じる
テーブル名、フォーム名、レポート名の〈_〉の左未整理ですが
　M_：マスターテーブル
　T_：トランザクションテーブル
　TMP_：テンポラリーテーブル
　S_；参照テーブル
　F_；フォーム
　Q_：クエリ
　R_：レポート

回答No.11

chayamati

添付は情報テーブルを〈T_売上〉として試作しました。
次のテーブルを追加しました
〈T_売上〉:店舗№、日付、商品名、金額、前半、後半
〈T_売上棚〉:店舗№、日付、商品名、金額、前半、後半、金額1、前半1、後半1、主鍵
〈TMP_売上〉:店舗№、日付、商品名、金額、前半、後半〈T_売上〉の集計用、主鍵
〈TMP_前月〉:店舗№、日付、商品名、金額、前半、後半〈T_売上〉の前月集計用、主鍵
前月に在って当月に無い月にデータは未完成です続けますか
ご存知と思いますが念の為
　店舗№、商品名はテーブルを用意してこれ参照（テーブルデザインでルックアップ）して、
　入力ミスを回避します
-------------------------------------------------------------------------------
Option Compare Database
Private Sub Form_Load()
基準日 = Date
日付RTN
End Sub
Private Sub 基準日_Exit(Cancel As Integer)
日付RTN
TMP_売上RTN
TMP_売上棚RTN
前月取込RTN
DoCmd.OpenReport "R_売上棚", acViewPreview
End Sub
Private Sub TMP_売上RTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into TMP_売上(日付,金額,店舗№, 商品名) select 日付,金額,店舗№,商品名 from T_売上 ")
DoCmd.RunSQL ("update T_売上 set 前半 = iif(day(日付)<=15,金額,0) ;")
DoCmd.RunSQL ("update T_売上 set 後半 = iif(day(日付)>15,金額,0) ;")
DoCmd.RunSQL ("delete from Tmp_売上")
DoCmd.RunSQL ("uPdate M_売上棚 set 主鍵 = 店舗№ & 商品名 ;")
End Sub
Private Sub TMP_売上棚RTN()
DoCmd.RunSQL ("delete from T_売上棚")
DoCmd.RunSQL ("insert into T_売上棚(店舗№,商品名,金額,前半,後半) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半) from T_売上 " _
& " where 日付 between forms!メニュー!月初 and forms!メニュー!月末 GROUP BY 店舗№,商品名;")
DoCmd.RunSQL ("uPdate T_売上棚 set 主鍵 = 店舗№ & 商品名 ;")
End Sub
Private Sub 日付RTN()
月初 = 基準日 - Day(基準日) + 1
月末 = 月初 + 31 - Day(月初 + 31)
月末1 = 月初 - Day(月初)
月初1 = 月末1 - Day(月末1) + 1
End Sub
Private Sub 前月取込RTN()
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from TMP_前月")
DoCmd.RunSQL ("insert into TMP_前月(店舗№,商品名,金額,前半,後半) select 店舗№,商品名,sum(金額),sum(前半),SUM(後半) from T_売上 " _
& " where 日付 between forms!メニュー!月初1 and forms!メニュー!月末1 GROUP BY 店舗№,商品名;")
DoCmd.RunSQL ("uPdate TMP_前月 set 主鍵 = 店舗№ & 商品名 ;")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 金額1= TMP_前月.金額 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 前半1= TMP_前月.前半 ; ")
DoCmd.RunSQL ("update T_売上棚 inner join TMP_前月 on T_売上棚.主鍵 = TMP_前月.主鍵 set 後半1= TMP_前月.後半 ; ")
End Sub
---------------------------------------------------------------------------------------

回答No.10

chayamati

今回１期間だけの回答です
処理結果を添付します

回答No.9

chayamati

ご質問の添付図をみていませんでした。
済みません
1.添付図の〈T_売上〉〈M_売上棚〉〈TMP_売上〉
　〈M_売上棚〉と〈TMP_売上〉には店舗№と商品名の組で主キーとする
　〈M_売上棚〉に店舗№と商品名の全ての組み合わせを登録する
2.クエリー〈Q_売上〉を作成
　2-1.抽出条件〈>=[forms]![メニュー]![月初] And <=[forms]![メニュー]![月末]〉
　2-2.前半: Sum(IIf([日付]<=[Forms]![メニュー]![中日],[金額],0))
　2-3.後半: Sum(IIf([日付]>[Forms]![メニュー]![中日],[金額],0))
3.〈Q_売上〉を〈TMP_売上〉にインポート
4.M_売上棚〉を〈TMP_売上〉にインポート
5.〈TMP_売上〉をレコードソースとしてフォームまたはレポートを作成
金額が０のフィールドはフォームまたはレポートで書式を〈###,###〉
6.新規に月初日、分日、月末日の日付ボックスを配置した〈メニュー〉フォームを作成
次のコードをVBA上に記述（コピペ）
　-------------------------------------------------------------------------------
Option Compare Database
Private Sub 中日_Exit(Cancel As Integer)
月初 = 中日 - Day(中日) + 1
月末 = 月初 + 31 - Day(月初 + 31)
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from TMP_売上")
DoCmd.RunSQL ("insert into TMP_売上(店舗№,商品名,金額,前半,後半) select 店舗№,商品名,合計,前半,後半 from Q_売上 ")
DoCmd.RunSQL ("insert into TMP_売上(店舗№,商品名) select 店舗№,商品名 from M_売上棚 ")
DoCmd.OpenReport "R_集計", acViewPreview
End Sub
-------------------------------
※商品名の〇▲◆並び替え出来ないようです