アクセスで困ってます(初心者)

質問

クエリのSQL文で作りたいと思っています。

表１
会社コード　年月日　　重量
＝＝　　　　＝＝　　　＝＝
１　　　　　5/1　　　　 １
１　　　　　5/12　　　　２
１　　　　　5/13 ３
２　　　　　6/21 ４
２　　　　　6/22 ５
・　　　　　　・　　　　・
・　　　　　　・　　　　・
このようなテーブルがあり21日締めでテーブルもしくは
クエリを作りたいと思います。

会社コード　5/21　5/22　5/23・・・・・・・・・6/20
＝＝　　　　＝＝　＝＝　＝＝
１　　　　　０　　０　　５
２　　　　　１　　３　　０
このようにデータを変換させたいと思います。集計クエリだと日付がない所が表示されないので・・・

どうぞ、よろしくお願いします。ｍ（○）ｍ

回答No.8

venzou · ベストアンサー

＞クエリのところで21日締めなのでクエリ日付を２１から翌月２０までととめたいのですがどうすればよろしいのでしょうか？

締め日は固定していないので、日付まで入れて下さい。
その日から１ヶ月の集計をする仕様になっています。

集計開始日に 2007/5/21 と日付まで入れると、
2007/5/21 ～ 2007/6/20 までになると思います。

============================================================
日付の入力を省略したいなら、下記の様に変更して下さい。

集計開始月に 2007/5 と月まで入力すれば、
2007/5/21 ～ 2007/6/20 までになると思います。

----集計日----
PARAMETERS 集計開始月 DateTime;
SELECT DateAdd("d", 番号+20, 集計開始月) as 集計日
FROM 連番
WHERE DateAdd("d", 番号+20, 集計開始月) < DateAdd("m", 1, DateAdd("d", 20, 集計開始月));
---------------

お礼

回答ありがとうございます。
こちらでちゃんと20日で区切れるようになりました。

補足

すみません。こちらの日付をひとつ気前からするにはどうしたらよいのでしょうか？
(例：2008/2　→　2008/01～2008/02)

回答No.9

venzou

＞すみません。こちらの日付をひと月前からするにはどうしたらよいのでしょうか？

----集計日----
PARAMETERS 集計開始月 DateTime;
SELECT DateAdd("d", 番号+20, DateAdd("m", -1, 集計開始月)) as 集計日
FROM 連番
WHERE DateAdd("d", 番号+20, DateAdd("m", -1, 集計開始月)) < DateAdd("d", 20, 集計開始月);
---------------

こんな感じになると思います。

老婆心ながら・・・
これくらいの変更は自力で出来ないと、今後の保守が大変ですよ。
頑張って勉強して下さいね。

回答No.7

noname#140971

isOK = CnnExecute("INSERT INTO tab1_temp (会社コード, 日付, 数量日計) VALUES ('" & strID(I) & "',#" & dteHiduke & "#," & _
　　　　　　　　　　　　　　　 DBSum("数量", "tab1", "会社コード='" & Trim(strID(I)) & "' AND 日付=#" & dteHiduke & "#") & ");")

ちと、DBSum の条件が不足していました。

2008/01/01 の集計値は 4でなく2。

回答No.6

noname#140971

示唆だけしてチョンも何ですので・・・。

tab1:

ID___日付____________会社コード__数量
1____2008/01/01_____________1________1
2____2008/01/01_____________2________2
3____2008/01/01_____________1________1
4____2008/01/19_____________2________2

tab1_temp:

会社コード___日付____________数量日計
___________1____2007/12/21____________0
___________1____2007/12/22____________0
・・・・
___________1____2008/01/01____________4
・・・・
___________2____2008/01/19____________2
___________2____2008/01/20____________0

次は、tab1からtab1_temp を発生させるVBAコードのサンプルです。

Const 集計開始日 = "2007/12/21"
Const 集計終了日 = "2008/01/20"

Private Sub コマンド0_Click()
　　Dim isOK　　　As Boolean
　　Dim I　　　　 As Integer
　　Dim N　　　　 As Integer
　　Dim Q　　　　 As Integer
　　Dim strID()　 As String
　　Dim dteHiduke As Date

　　' ---------------------
　　'　会社コードを配列に代入
　　' ---------------------
　　strID() = Split(DBSelect("SELECT 会社コード FROM tab1 GROUP BY 会社コード"), ";")
　　' -------------------
　　'　一時ファイルを初期化
　　' -------------------
　　CnnExecute "DELETE * FROM tab1_temp"
　　' -------------------
　　'　日付毎・会社毎に集計
　　' -------------------
　　isOK = True
　　N = UBound(strID()) - 1
　　dteHiduke = CDate(集計開始日)
　　Do
　　　　For I = 0 To N
　　　　　　isOK = CnnExecute("INSERT INTO tab1_temp (会社コード, 日付, 数量日計) VALUES ('" & strID(I) & "',#" & dteHiduke & "#," & _
　　　　　　　　　　　　　　　 DBSum("数量", "tab1", "日付=#" & dteHiduke & "#") & ");")
　　　　　　If Not isOK Then
　　　　　　　　Exit For
　　　　　　End If
　　　　Next I
　　　　dteHiduke = dteHiduke + 1
　　Loop Until dteHiduke > CDate(集計終了日) Or isOK = False
　　If isOK Then
　　　　MsgBox "一時テーブルの作成を問題なく終了しました。"
　　End If
End Sub

・先ず、会社コードを配列変数に取得。
・その後、期間を集計するループに突入。
・一日毎、各会社毎の集計値を一時テーブルに書き込んでいます。

DBSelect関数、CnnExecute関数、DBSum関数などはこのような処理用のために用意した自作関数です。
このようなVBAに挑戦されるのであれば３つの自作関数は補足します。

回答No.5

CHRONOS_0

クロス集計クエリですね
列見出しプロパティーのところに全ての日付を記入してやれば
できますよ

回答No.4

venzou

>集計クエリだと日付がない所が表示されないので・・・

テーブルにないデータを、クエリで処理するのは難しいです。

日付の一覧をテーブル等に用意する必要がありますが、
毎月変化するので、少し面倒ですね。

VBAを使わない方法で、出来るだけ簡単な方法を考えてみました。
下記の方法は、集計開始日を入力するだけで、
１ヶ月分のクロス集計が行えます。

以下、準備の手順。
（少し長いですが、最初の１回だけですので、試してみて下さい）
============================================================
まず、テーブルを用意します。
テーブル名：連番
フィールド名：番号（数値型）主キー

このテーブルに、0～30までの数値を登録しておきます。
（１ヶ月分の日付を作るためのデータです）

下記のクエリで集計開始日からの１ヶ月の日付の一覧が作れます。
（集計開始日はパラメータにしてあります）
このクエリを「集計日」と言う名前で保存したとします。

----集計日----
PARAMETERS 集計開始日 DateTime;
SELECT DateAdd("d", 番号, 集計開始日) as 集計日
FROM 連番
WHERE DateAdd("d", 番号, 集計開始日) < DateAdd("m", 1, 集計開始日);
--------------

「集計日」クエリと表１を結合させ、集計用の元になるクエリを作ります。
このクエリを「集計用データ」と言う名前で保存したとします。
（ここで、外部結合（LEFT JOIN）を使うのがポイントです。）

----集計用データ----
SELECT 集計日.集計日, 表１.*
FROM 集計日 LEFT JOIN 表１ ON 集計日.集計日 = 表１.年月日;
--------------------

「集計用データ」クエリを元にクロス集計クエリを作ります。

----クロス集計----
TRANSFORM Sum(集計用データ.重量) AS 重量の合計
SELECT 集計用データ.会社コード, Sum(集計用データ.重量) AS [合計 重量]
FROM 集計用データ
GROUP BY 集計用データ.会社コード
PIVOT Format([集計日],"Short Date");
------------------

============================================================
すこし長いですが、これで準備完了です。

後は、クロス集計を開き、集計開始日を入力するだけで、
１ヶ月の集計が出来ます。

--------------------------------------
補足：SQL文からクエリを作る方法
クエリの新規作成　→　デザインビュー
テーブルは追加しない
表示　→　SQLビュー
SQL文をコピー＆ペースト

お礼

解答ありがとうございます。
クエリのところで21日締めなのでクエリ日付を２１から翌月２０までととめたいのですがどうすればよろしいのでしょうか？

回答No.3

noname#140971

Q、のようにデータを変換させたいと思います。
Ａ、普通は、このような操作はしないと思います。

月締めの表の体裁のために元々のデータを加工するのは邪道と思いますよ。
さりとてSQL文でやるのは至難かと思います。
残された手段は、クエリ一発で実現可能な状況を作り出すこと。

会社コード
年月日
重量日計

例えば、月末締めの一時テーブルに書き出せば後は簡単です。

SQLでDELETE文を発行し一時テーブルをクリアする。
日付毎に集計して書き込む。

こういう方法がお勧めです。

回答No.2

noname#60992

ＶＢＡが使えれば、手で入れる必要はありませんが、、、

手で入れるなら、会社コード0など存在しないものを作り、日付に対してのデータを全部0にしておけば、データがないところはブランク表示になりそうです。
これも、VBAが使えれば、開始日と終了日を指定してループさせながら
INSERT INTO 表1 VALUES(0,対象日,0)
のようなＳＱＬを発行していくことでできます。

回答No.1

noname#60992

すべての会社コードのすべての日付に重量=0というレコードを作って集計されてはいかがでしょうか？

お礼

早速の回答ありがとうございます。
すいません。何十とある会社コードのすべての日付(あいている日付)
に０を登録していく手間を考えるとちょっと無理です。
データ量も膨大になるわけで・・・
何とかＳＱＬ等でデータのまとめを簡単にしたいのです