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 回答日時： 2009/06/20 20:44

アクセスの処理方法の

（１）クエリ操作や関数
（２）SQL
（３）モジュール
のうち
（１）は多分無理かと思う。
（２）SQLで解決するのも、難しいと思う。それで（３）でやってみたまた、結果のアウトプットは、レポートでは非連結では難しそうなので、EXCELを使うことにした。これでは初めからEXCELにエクスポートして、EXCEL関数やVBAでやるほうがよいかもしれないが。
ーーー
私のテスト例
社員2.mdbと言うｍｄｂがあり、「室料」と言うテーブルを作った。
データは
ID開始日終了日室料計
12009/04/202009/06/031000000
22009/03/052009/06/232000000
32008/12/212009/03/053000000
を作った。
ーーー
モジュールに
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　11244444
2009/5/12009/5/31　31688889
2009/6/12009/6/3　366667
-----
2009/3/52009/3/31　27486486
2009/4/12009/4/30　30540541
2009/5/12009/5/31　31558559
2009/6/12009/6/23　　23414414
-----
2008/12/21　2008/12/3111440000
2009/1/12009/1/31　311240000
2009/2/12009/2/28　281120000
2009/3/12009/3/5　5200000
-----
A,B列は書式を「日付に設定しておく。
パスのXXXXは私に場合ユーザーIDです。
内分割計算（シェア計算）しているので、合計が１ぐらい合わない場合が出るが、丸めのことは考慮してない。
For i = 1 To 120の120はとりあえずテストでの限界になっているので注意してください。

No.2 回答者： mitarashi 回答日時： 2009/06/20 11:54

具体的なテーブルの構造が分からないので、場所貸し案件毎に、なんらかのIDがあるとして、別にID、年月、当月貸与日数、場所代IDといったテーブルを作り、VBAで1レコードずつ変換してやって、変換先のテーブルで集計する事くらいしか思いつきません。

変換はエクセルでやる方が楽でしょう。
特定の1年に限定すれば、ユーザー定義関数を作ってやって、クエリでできない事もないかもしれませんが、年がまたがると思うので、フィールド数が不定で現実的ではないでしょうね。

この回答へのお礼

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

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

お礼日時：2009/06/22 17:53

No.1 回答者： umazanpai 回答日時： 2009/06/20 07:28

とりあえず「開始日」　　　　「終了日」

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

この回答へのお礼

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

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

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

お礼日時：2009/06/22 17:50