アクセスで前年対比を出す方法を教えてください。

質問

売上テーブルにID、日付、A店舗売上額、B店舗売上額、・・・を入れています。このデータから月初から当日までの累計と前年同月同日対比を表示させたいのですが、どうすればいいでしょう？その日だけではなく、昨日、一昨日・・・のデータも表示させたいのですが。今まではEXELでやっていたのですが、月が変わるたびに、シートを増やし、一昨年のシートからデータを取るという作業があるのと、別のEXELのファイルに簡単な損益計算書を作っているのですが、そのデータをコピーする手間を省きたいと考えてます。またこの損益計算書も同じアクセスのファイル（データベース）で作りたいと考えています。

回答No.3

ema_0222 · ベストアンサー

長い回答になり申し訳ありません。
前提として、今年と昨年のデータが構造が同じテーブルに入力されているとします。peace510さんはクエリ、レポートは使えるが、フォームやモジュールは使えないと思って書いています。
[]で括られているものはテーブル名など総称を意味します。

まず、今年の売上をクエリで日付の抽出をします。抽出条件は　Between DateSerial(Year(Now()),Month(Now()),1) And Now()　←今月の初日から今日までという意味です。
抽出条件を入力したらSQLビュー(表示→SQLビュー)にしてFROMの前でSERECT行の最後に「, format([テーブル名].[日付フィールド名],"mmdd") as [新しいフィールド名]」と入力します。←日付データを月日だけにして新しいフィールドを作ります。

そのクエリの名前を「Q_今年売上」とするとQ_今年売上を基にクエリを作ります。
名前を「Q_今年売上合計」とするとQ_今年売上のIDと日付を除いた、売上データのみをフィールドに選択して、Σ(集計)ボタンを押し、集計の欄を「合計」にします。
フィールド名が「～の合計」となるので気になるようならばSQLビュー(表示→SQLビュー)にして、「Sum(Q_今年売上.[フィールド名]) AS フィールド名の合計」などの「AS」の後ろを変更します。

これで、Q_今年売上が期間抽出されたデータ、Q_今年売上合計が期間抽出されたデータの合計(当日までの累計)になります。

次に、昨年の売上をクエリで日付の抽出をします。抽出条件は　Between DateSerial(Year(Now())-1,Month(Now()),1) And DateSerial(Year(Now())-1,Month(Now()),Day(Now()))　←今年-1の同月初日から同月同日まで
抽出条件を入力したらSQLビュー(表示→SQLビュー)にしてFROMの前でSERECT行の最後に「, format([テーブル名].[日付フィールド名],"mmdd") as [新しいフィールド名]」と入力します。←新しいフィールドを作ります。
そのクエリの名前を「Q_昨年売上」とでもしておきます。

次にQ_今年売上とQ_昨年売上を基にクエリを作ります。さきほど作った新しいフィールド（月日だけを抽出したもの）を結合させて、結合プロパティを「Q_今年売上の全レコードとQ_昨年売上の同じ結合フィールドのレコードだけを含める」にします。
フィールドに入れる値を選択しますが、日付はQ_今年売上の日付を入れると良いでしょう。フィールド名の変更は合計を出したところで説明しています。
このクエリの名前を「Q_売上比較」とでもします。

Q_売上比較と、Q_今年売上合計を基にレポートを作ります。このクエリには月毎のすべての売上が含まれますので、あとは加工次第ですね。抽出の範囲を変更したいときは、ヘルプを見ながら勉強してください。

あとの演算はがんばってください。

クエリだけに頼らずに、モジュールも使うと楽にいろんなことができますよ。

お礼

ありがとうございます。このページ初めて使ったので、補足にも同じ内容を入れてしまいました。読んだだけではよくわからないので、一度試してみます。フォームも同じ要領でいいんですね。モジュールは基本的にほとんどイベント型のサブプロシージャのみを作っています。DAOからADOに変わって、他データベースに接続したりするのが、こんがらがってます。Functionプロシージャ－は

Function topday() As Date '現在の月の月初（1日）を計算します。
Dim firstdate As Date ' 変数を宣言します。
Dim intervaltype As String
Dim number As Integer
intervaltype = "d" ' "d" によって追加する時間間隔として、日を指定します。
firstdate = Date - Day(Date)
number = 1
topday = DateAdd(intervaltype, number, firstdate)
End Function

ぐらいでしょうか？もしよろしければ、モジュールでの作り方も教えていただければ、うれしいです。SQLはちょっと苦手です。ありがとうございました。

補足

ありがとうございます。読んだだけではよくわからないので、一度試してみます。フォームも同じ要領でいいんですね。モジュールは基本的にほとんどイベント型のサブプロシージャのみを作っています。DAOからADOに変わって、他データベースに接続したりするのが、こんがらがってます。Functionプロシージャ－は

Function topday() As Date '現在の月の月初（1日）を計算します。
Dim firstdate As Date ' 変数を宣言します。
Dim intervaltype As String
Dim number As Integer
intervaltype = "d" ' "d" によって追加する時間間隔として、日を指定します。
firstdate = Date - Day(Date)
number = 1
topday = DateAdd(intervaltype, number, firstdate)
End Function

ぐらいでしょうか？もしよろしければ、モジュールでの作り方も教えていただければ、うれしいです。SQLはちょっと苦手です。ありがとうございました。

回答No.2

Hk2001

前年対比の場合は、状況によって異なります。
(1)対象範囲の本年のデータを基準にしてしまっていいのなら簡単です。
(2)しかし、前年のデータが存在して、本年のデータがない場合を表示させたい
などの条件が入る場合は、コーディングで配列などにデータを落としたり、
ワークテーブルなどに一時保存して　出力の度データを作成してやる必要があります。

(1)対象範囲の本年のデータを基準にしてしまっていい場合は
クエリをいくつか作ってやり（本年、昨年、一昨年など）
それらをクエリで再度結合してやればできます。

(2)の場合は、まず本年のデータを作成、次に昨年のデータを同店舗同月同日のデータがあれば、昨年の格納場所に保存、なければ、データを追加という風にやって生きます。

なんにしろ、アクセスなら、プログラムを組めば体外のことはできます。

お礼

ありがとうございます。参考にさせて頂きます。アクセスは奥が深いので、大好きです。ただ、没頭しないと出来ないので、なかなか作ることが少なく、後で自分の作ったデータベース、特にVBAは自分でもわからず、なかなか進歩しない毎日です。集計や分析はEXELが得意かと思いその必要のあるものは、ほとんどEXELでやってしまいます。今回は入力は私がしていないので、月ごとにシートを増やすのが手間なので、新しくチャレンジしようと思いました。参考本の在庫データベースでも、過去の残高（昨日の残高、一昨日の残高など）は表示せず、今日現在（最終レコード）の残高を表示させるようにしているので、アクセスではこういううのは難しいと思ってました。早速試してみます。ありがとうございました。

回答No.1

i-harada

i-haradaです。

邪道？なのかもしれませんが、我流の方法として「今年テーブル」の日付をクエリーで分解しくっ付けて「月／日」を作ります。前年テーブルも同じようにクエリー上で「月／日」を作り同じクエリー上でリレーションで繋ぎ対比させたいフィールドを表示させて、％をだす。抽出は基の日付でbetweenでどうでしょうか？
それを元にレポートを作ればＯＫ？
試してないので他に手段がなければやって見て下さい。

やっぱ邪道だな！＼(_ _)

お礼

ありがとうございます。他の人の回答を見せていただくと、邪道ではないのではないでしょうか？だけど僕も我流でアクセスはやってるので、結構邪道っぽいのも好きです。間違ってるかもしれませんが、アクセスは自分の求めている便利さを追求できれば、その作る過程が楽しいので、少しぐらい邪道でもよしと思っています。これが、お金をもらってつくるのなら、そういう訳には行かないでしょうけれど、ありがとうございました。