accessの集計について

質問

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

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

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

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

回答No.8

chayamati

＞クロス集計で出来そうな気がしますが
　もしくはvba使わないと不可能でしょうか？
★大丈夫です。
　Accessでは一つのテーブルに数年にわたって情報を処理します
　そのため月初、中日、月末の３つの日付が必要です
　このクエリーを開くと、この３つの入力枠が次々と表示されます。
　添付図をご覧ください。次の3式を入力します
　他の項目はそれぞれの枠の右端を右クリックからの選択クリックです
　　①　前半: Sum(IIf([日付]<=[中日],[金額],0))
　　②　後半: Sum(IIf([日付]>[中日],[金額],0))
　　③　>=[月初] And <=[月末]

回答No.7

chayamati

回答№6で添付図はクエリの結果です。
　このクエリをレコードソースとするフォームまたはレポートを作成して報告書としますと
　お伝えしましたが
クエリを開くとうまく表示されませ。
そこで下記のように情報の加工を繰り返すようVBAを追記しました
T_売上→Q_売上→TMP_売上→F_売上集計
-----------------------------------------------------------------------------------------
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.OpenForm "F_売上集計"
End Sub
---------------------------------------------------------------------------------
添付図の報告書になりました
集計ボックスに配置しました
ご不明な点はご遠慮なく補足質問下さい

回答No.6

chayamati

回答№2です
如何ですか
ExcelならSheetを月別に用意して処理しますが
Accessでは複数年にわたって処理するので
　月初、分日、月末の日付の条件を用意します
添付図はクエリの結果です。
このクエリをレコードソースとするフォームまたはレポートを作成して報告書とします

前回の手順の補足です
1.手順説明の読み方
・〈手順名〉内の手順名は左クリックです。
　その他の処理は入力、ドラッグ、右クリック等追記しています
2.テーブルの項目
・主キー：ID　オートナンバー型
・登録日：日付/時刻型、規定値=date()
・マスターテーブルの商品名、顧客名等
　1.インデックスプロパティーで重複なし
　2.振り仮名機能活用
3.テーブル名等用途により頭にM_ T_ S_ Q_ F_ R_を付けています
・M_:マスターテーブル
・T_:トランザクションテーブル
・S_：参照テーブル
・Q_：クエリー
・F_：フォーム
・R_：レポート
・TMP:テンポラリテーブル、一時テーブル

回答No.5

m3_maki

あ、前半・後半では逆転しますね。修正。

TRANSFORM Sum(集計_サブ.金額) AS 金額の合計
SELECT 集計_サブ.店舗No, 集計_サブ.商品情報
FROM 集計_サブ
GROUP BY 集計_サブ.店舗No, 集計_サブ.商品情報
PIVOT IIf(Day([日付])<16,Format([日付],"yyyy\年mm""'月15日まで'"""),Format([日付],"yyyy\年mm""'月16日から月末
'"""));

回答No.4

m3_maki

こんな感じで。

TRANSFORM Sum(集計_サブ.金額) AS 金額の合計
SELECT 集計_サブ.店舗No, 集計_サブ.商品情報
FROM 集計_サブ
GROUP BY 集計_サブ.店舗No, 集計_サブ.商品情報
PIVOT IIf(Day([日付])<16,Format([日付],"yyyy\年mm""'月前半'"""),Format([日付],"yyyy\年mm""'月後半'"""));

IIf 以下を書き換えれば、もっと複雑な列見出しにもできます。
月を「m」と一桁表示にすると、10月が前に出てしまうので
逆転しないように2桁表示にしています。
１桁かつ９月の次に１０月にしたいなら、VBA で SQL の書き換えが必要。

さらに、もし 店舗No・商品情報の組み合わせで実際に出てこないものがある場合、
店舗No・商品情報をクロス結合したものを、クエリに加えて
そちらを集計に使うようにする。

クロス集計は結構っ面倒なんですよ。

回答No.3

yumi-access

VBAを使う方がいろいろできて便利ですが、クエリでも出来そうに思います。

・クエリを集計クエリにして、店舗と商品でグループ化して
金額はsumで日付はwhere条件にすれば期間での集計できると思います。

回答No.2

chayamati

＞クロス集計で出来そうな気がしますが、
　店舗別、商品別にして半月毎に集計する事は可能でしょうか？
★クロス集計で出来ます。
　クエリを開く前に、抽出条件の日付関係（月初、中日〈ナカビ〉、月末）
　を確定しておく必要があります。
1.この中日、月初、月末順にを空のメニューフォームに配置します
　今日は11月10日です、ここから10日引くと10月31になります
　この理屈を踏まえて月初、月末を確定させT_売上をレコードソースするQ_売上を開きます。
　メニューの中日のプロパティーのイベントタグのフォーカス喪失時右端の…より
　コードビルダを立ち上げ以下をコピペします。
　-----------------------------------------------------------
Option Compare Database
Private Sub 中日_Exit(Cancel As Integer)
月初 = 中日 - Day(中日) + 1
月末 = 月初 + 31 - Day(月初 + 31)
DoCmd.OpenQuery "Q_売上"
End Sub
----------------------------------------------------------
2.クロス集計のQ_売上クエリを作成
　添付図の順に進めます
①T_売上を表示
②〈右クリック〉→〈クエリの種類〉→〈クロス集計〉
③→〈店舗№〉→〈商品名〉
④→〈金額の合計:金額〉→〈合計〉
⑤→〈前半:Sum(IIf([日付]<=[Forms]![メニュー]![中日],[金額],0))〉→〈演算〉
⑥→〈後半:Sum(IIf([日付]>[Forms]![メニュー]![中日],[金額],0))〉→〈演算〉
⑦→〈日付〉→〈where条件〉
　→〈>=[forms]![メニュー]![月初] And <=[forms]![メニュー]![月末]

回答No.1

bardfish

まず、AccessのクエリというのはSQLをGUI化したものに過ぎません。
SQLで実現できなければクエリでも実現できません。

問題となるのは「月の前後半」と言う部分。
店舗・商品毎に金額を集計したいとんなれば集計関数とグループ化をすればいい事になるのですが、グループ化で使う日付が生のままだと前後半を判断できる関数や演算子はないはずです。
従って、現状のままでは出来ないという事になります。

集計する為には日付から年月毎に前後半を判断できるフラグみたいな項目を一つ追加できればあとは簡単です。

私がよくやる手法としては、GROUP BY(集計クエリというものになるのかな？)だけのSELECT(一般的なクエリ)で可能な一時的に使用するテーブルを作って、そのテーブルに対して必要なデータを加工して追加してやるというルーチン(VBA。Accessではモジュールだったはず)を作り必要なタイミングで実行できるフォームを用意します。

そもそもの話になってしまいますが、AccessというのはRDBMS(リレーショナルデータベース)ですので、一番最初のテーブル設計の完成度によってそのあとの難易度が大きく変わってきます。
質問を見ただけの率直な感想ですが、AccessをExcelみたいな感覚で使い始めたのが躓きの原因だと思います。テーブル設計の前にどんなデータを元にどんな結果を得たいのかを要件定義という形にして明確にします。
運用していくと当初の要件には含まれない事が出てきてクエリ一発では不可能なデータが必要とされるケースも多々あります。だけど実現する為の項目は揃っている･･･そう言うときにVBA(モジュール)を使用して予定になかったデータの加工を実現する事になります。

Accessの各種クエリでなにが出来てなにが出来ないかを知る最も簡単な方法の一つとしてSQLServerのSQLを勉強する。が一番近道かもしれません。
SQLServer自体はMicrosoftのサイトから無料で利用できるエディションがダウンロードできます。
ですがSQLServerを利用する為のコンソールアプリは別途ダウンロードが必要でこのあたりがOfficeソフトしか利用した事がない人にとってはいきなり敷居が高くなると思います。
ある程度TCP/IPの知識も必要になってくるので敷居は更に高くなります。
SQLServerのテーブルをAccessのリンクテーブルとして利用する事も出来ますか。
それが出来れば、Accessのデータベースファイルのファイルサイズの上限をほぼ気にする事なくデータベースを利用する事が出来ます。
Accessの32bit版だとデータベースファイルのファイルサイズの上限は4GBだったと思います。
作業用テーブルを使用し頻繁にレコードの追加と削除を行っていくとファイルサイズはあっという間に上限に達します。レコードを削除してもファイルサイズは減りません。その為にデータベースファイルの最適化？という機能が備わっています。

とにかく、テーブル設計/デーダーヘス設計が甘いほど後で苦労するのがリレーショナルデータベースです。
頑張って苦労してください。
苦労した分だけエキスパートへの道が開けるのです(笑)
行き着く先はSQLServer + VisualStudioのExpressエディションかもしれませんが、それはデータベースシステムの入り口でしかありません。この手のものにはゴールというモノがありそうで実はありません(笑)

資格試験なんて、その試験が制定された時期の目安としての目標でしかありません。
その後には更に高度なモノが登場するのですから認定試験というのはその度に再取得しなくてはならない無間地獄のようなモノ。
だったら、本質だけを深く理解しておけば新しい何かが登場したとしても普通に使いこなせる可能性の方が高い。資格認定試験は該当バージョンのアプリケーションソフトウェア群の使い方について評価されるモノという認識、つまりはセールスの一環なので特定製品以外は使えないというポンコツエンジニア(だが狭いポイントに特化すれば優秀)を量産するだけというイメージが･･･