• ベストアンサー

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

クエリのSQL文で作りたいと思っています。 表1 会社コード 年月日  重量 ==    ==   == 1     5/1     1 1     5/12    2 1     5/13 3 2     6/21 4 2     6/22 5 ・      ・    ・ ・      ・    ・ このようなテーブルがあり21日締めでテーブルもしくは クエリを作りたいと思います。 会社コード 5/21 5/22 5/23・・・・・・・・・6/20 ==    == == == 1     0  0  5 2     1  3  0 このようにデータを変換させたいと思います。集計クエリだと日付がない所が表示されないので・・・ どうぞ、よろしくお願いします。m(○)m

質問者が選んだベストアンサー

  • ベストアンサー
  • venzou
  • ベストアンサー率71% (311/435)
回答No.8

>クエリのところで21日締めなのでクエリ日付を21から翌月20までととめたいのですがどうすればよろしいのでしょうか? 締め日は固定していないので、日付まで入れて下さい。 その日から1ヶ月の集計をする仕様になっています。 集計開始日に 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, 集計開始月)); ---------------

rnyrevo
質問者

お礼

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

rnyrevo
質問者

補足

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

その他の回答 (8)

  • venzou
  • ベストアンサー率71% (311/435)
回答No.9

>すみません。こちらの日付をひと月前からするにはどうしたらよいのでしょうか? ----集計日---- PARAMETERS 集計開始月 DateTime; SELECT DateAdd("d", 番号+20, DateAdd("m", -1, 集計開始月)) as 集計日 FROM 連番 WHERE DateAdd("d", 番号+20, DateAdd("m", -1, 集計開始月)) < DateAdd("d", 20, 集計開始月); --------------- こんな感じになると思います。 老婆心ながら・・・ これくらいの変更は自力で出来ないと、今後の保守が大変ですよ。 頑張って勉強して下さいね。

noname#140971
noname#140971
回答No.7

      isOK = CnnExecute("INSERT INTO tab1_temp (会社コード, 日付, 数量日計) VALUES ('" & strID(I) & "',#" & dteHiduke & "#," & _                 DBSum("数量", "tab1", "会社コード='" & Trim(strID(I)) & "' AND 日付=#" & dteHiduke & "#") & ");") ちと、DBSum の条件が不足していました。 2008/01/01 の集計値は 4でなく2。

noname#140971
noname#140971
回答No.6

示唆だけしてチョンも何ですので・・・。 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に挑戦されるのであれば3つの自作関数は補足します。

  • CHRONOS_0
  • ベストアンサー率54% (457/838)
回答No.5

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

  • venzou
  • ベストアンサー率71% (311/435)
回答No.4

>集計クエリだと日付がない所が表示されないので・・・ テーブルにないデータを、クエリで処理するのは難しいです。 日付の一覧をテーブル等に用意する必要がありますが、 毎月変化するので、少し面倒ですね。 VBAを使わない方法で、出来るだけ簡単な方法を考えてみました。 下記の方法は、集計開始日を入力するだけで、 1ヶ月分のクロス集計が行えます。 以下、準備の手順。 (少し長いですが、最初の1回だけですので、試してみて下さい) ============================================================ まず、テーブルを用意します。 テーブル名:連番 フィールド名:番号(数値型)主キー このテーブルに、0~30までの数値を登録しておきます。 (1ヶ月分の日付を作るためのデータです) 下記のクエリで集計開始日からの1ヶ月の日付の一覧が作れます。 (集計開始日はパラメータにしてあります) このクエリを「集計日」と言う名前で保存したとします。 ----集計日---- PARAMETERS 集計開始日 DateTime; SELECT DateAdd("d", 番号, 集計開始日) as 集計日 FROM 連番 WHERE DateAdd("d", 番号, 集計開始日) < DateAdd("m", 1, 集計開始日); -------------- 「集計日」クエリと表1を結合させ、集計用の元になるクエリを作ります。 このクエリを「集計用データ」と言う名前で保存したとします。 (ここで、外部結合(LEFT JOIN)を使うのがポイントです。) ----集計用データ---- SELECT 集計日.集計日, 表1.* FROM 集計日 LEFT JOIN 表1 ON 集計日.集計日 = 表1.年月日; -------------------- 「集計用データ」クエリを元にクロス集計クエリを作ります。 ----クロス集計---- TRANSFORM Sum(集計用データ.重量) AS 重量の合計 SELECT 集計用データ.会社コード, Sum(集計用データ.重量) AS [合計 重量] FROM 集計用データ GROUP BY 集計用データ.会社コード PIVOT Format([集計日],"Short Date"); ------------------ ============================================================ すこし長いですが、これで準備完了です。 後は、クロス集計を開き、集計開始日を入力するだけで、 1ヶ月の集計が出来ます。 -------------------------------------- 補足:SQL文からクエリを作る方法 クエリの新規作成 → デザインビュー テーブルは追加しない 表示 → SQLビュー SQL文をコピー&ペースト

rnyrevo
質問者

お礼

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

noname#140971
noname#140971
回答No.3

Q、のようにデータを変換させたいと思います。 A、普通は、このような操作はしないと思います。 月締めの表の体裁のために元々のデータを加工するのは邪道と思いますよ。 さりとてSQL文でやるのは至難かと思います。 残された手段は、クエリ一発で実現可能な状況を作り出すこと。 会社コード 年月日 重量日計 例えば、月末締めの一時テーブルに書き出せば後は簡単です。 SQLでDELETE文を発行し一時テーブルをクリアする。 日付毎に集計して書き込む。 こういう方法がお勧めです。

noname#60992
noname#60992
回答No.2

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

noname#60992
noname#60992
回答No.1

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

rnyrevo
質問者

お礼

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

関連するQ&A