Accessで、複数月にわたる売上を月ごとに集計したいです

質問

こちらでは初めて質問させていただきます。

Accessは簡単なマクロとVBを作ったことがあるくらいの初級者です。

■やりたいこと
　複数月にわたる場所貸しの日数を求め、その売上を月ごとに集計したい。（場所代は毎日一定で、日にちによる変化はありません）

■詳細
　現在、テーブルには「開始日」「終了日」「売上」の項目があります。
売上は、開始日から終了日までの日数を出し、その日数で割って算出したいです。（端数は最後の日に上乗せします）

例えば、
「開始日」　　　　「終了日」　　　　　「売上」
2009/04/20 　　 2009/06/03 　　 1,000,000

のような場合、日数はDatediff関数で求めれば良いと思うのですが、
「4月の売上のみ集計」「5月の売上のみ集計」といった場合の
算出の仕方がわかりません。
例えば4月は11日間、5月は30日間、6月は3日間となり、100万円をそれぞれの日数分で割れば良いと思っています。

こういったデータから、月ごとの集計を出すのにはどのようにすれば良いでしょうか？
Accessのフォームには「2009/06」などや、「2009/06から6ヶ月分」などのように入力して算出する予定です。

わかりづらい内容がございましたら補足させていただきます。
何卒宜しくお願い致します。

回答No.3

imogasi

アクセスの処理方法の
（１）クエリ操作や関数
（２）SQL
（３）モジュール
のうち
（１）は多分無理かと思う。
（２）SQLで解決するのも、難しいと思う。それで（３）でやってみたまた、結果のアウトプットは、レポートでは非連結では難しそうなので、EXCELを使うことにした。これでは初めからEXCELにエクスポートして、EXCEL関数やVBAでやるほうがよいかもしれないが。
ーーー
私のテスト例
社員2.mdbと言うｍｄｂがあり、「室料」と言うテーブルを作った。
データは
ID 開始日 終了日 室料計
1 2009/04/20 2009/06/03 1000000
2 2009/03/05 2009/06/23 2000000
3 2008/12/21 2009/03/05 3000000
を作った。
ーーー
モジュールに
Sub test013()

Dim oExl As Object
Dim oExlBook As Object

'Excelファイルオープン
Set oExl = CreateObject("Excel.Application")
oExl.Application.Visible = True
Set oExlBook = oExl.Application.Workbooks.Open("C:\Documents and Settings\XXXX\My Documents\test01.xls")
k = 2
'---
Dim moji As String
Dim URL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "室料", cn, adOpenStatic, adLockPessimistic
'---------
If rs.RecordCount = 0 Then
MsgBox "該当するレコードは存在しません"
Else
Do
oExlBook.sheets("Sheet3").range("A" & k) = rs.Fields("開始日")
situttl = rs.Fields("室料計")
sy = Year(rs.Fields("開始日"))
sm = Month(rs.Fields("開始日"))
sd = Day(rs.Fields("開始日"))
st = rs.Fields("開始日")
ed = rs.Fields("終了日")
tns = ed - st + 1
'---
For i = 1 To 120
matu = DateSerial(sy, sm + i, 0)
If matu < rs.Fields("終了日") Then
oExlBook.sheets("Sheet3").range("B" & k) = matu
ns = (matu - st + 1)
oExlBook.sheets("Sheet3").range("C" & k) = matu - st + 1
oExlBook.sheets("Sheet3").range("D" & k) = Int(situttl * ns / tns + 0.5)
st = DateSerial(sy, sm + i, 1)
Else
oExlBook.sheets("Sheet3").range("B" & k) = rs.Fields("終了日")
oExlBook.sheets("Sheet3").range("C" & k) = (rs.Fields("終了日") - st + 1)
ns = (rs.Fields("終了日") - st + 1)
oExlBook.sheets("Sheet3").range("D" & k) = Int(situttl * ns / tns + 0.5)
GoTo p1
End If
k = k + 1
oExlBook.sheets("Sheet3").range("A" & k) = matu + 1
Next i
p1:
rs.MoveNext
k = k + 1
oExlBook.sheets("Sheet3").range("B" & k) = "-----"
k = k + 1
Loop Until rs.EOF
End If
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
'----
'Excel終了
oExlBook.Close True
oExl.Application.Quit
Set oExlBook = Nothing
Set oExl = Nothing
End Sub
を作った。
test01.xlsは白紙（データなし）のものを作っておく。
実行するとEXCELのシートに（Sheet3)
A列　　　B列　　　C列　　　　D列
2009/4/20　 2009/4/30　 11 244444
2009/5/1 2009/5/31 　31 688889
2009/6/1 2009/6/3 　3 66667
-----
2009/3/5 2009/3/31　 27 486486
2009/4/1 2009/4/30　 30 540541
2009/5/1 2009/5/31　 31 558559
2009/6/1 2009/6/23 　　23 414414
-----
2008/12/21　2008/12/31 11 440000
2009/1/1 2009/1/31 　31 1240000
2009/2/1 2009/2/28 　28 1120000
2009/3/1 2009/3/5 　5 200000
-----
A,B列は書式を「日付に設定しておく。
パスのXXXXは私に場合ユーザーIDです。
内分割計算（シェア計算）しているので、合計が１ぐらい合わない場合が出るが、丸めのことは考慮してない。
For i = 1 To 120の120はとりあえずテストでの限界になっているので注意してください。

回答No.2

mitarashi

具体的なテーブルの構造が分からないので、場所貸し案件毎に、なんらかのIDがあるとして、別にID、年月、当月貸与日数、場所代IDといったテーブルを作り、VBAで1レコードずつ変換してやって、変換先のテーブルで集計する事くらいしか思いつきません。変換はエクセルでやる方が楽でしょう。
特定の1年に限定すれば、ユーザー定義関数を作ってやって、クエリでできない事もないかもしれませんが、年がまたがると思うので、フィールド数が不定で現実的ではないでしょうね。

お礼

mitarashi様、書き込みありがとうございます。
会社でしかこのサイトが見られないため、お礼が遅くなってしまって申し訳ありません。

やはりクエリなどだけでは難しいということですね。
1件ごとにデータを取り出すたびになんらかの処理を入れるという方向で考えてみたいと思います。
良いヒントをいただきました！！
ありがとうございますm(__)m

回答No.1

umazanpai

とりあえず「開始日」　　　　「終了日」
の間の日を1日1レコードにします。
テーブルsuujiを数字0～9で新規作成
これで４５レコードつくります。
あとは。。。。

お礼

umazanpai様、早速の書き込みを頂き、有難うございます！
会社でしかOKWEBを見られないため、お礼が今日になってしまったことをお許しください。

この画面には画像も付けられるんですね！驚きました。
わかりやすいように付けてくださってありがとうございます。
書き込みしてくださった内容をこれからよく見て、チャレンジしてみたいと思います。
また結果を書き込みさせていただきます。

簡単ではございますが、まずはお礼まで‥m(__)m