Access2000 集計とレポートの方法について

私の知識では何ともならず、日々苦労しておりますので、お助け頂きたく宜しくお願い致します。

Access2000のテーブルには売り上げ実績として
日付、担当者、商品大区分、商品小区分、在庫
(Date、text、text、text、数値）
がデータとして登録されています。例としては、
2008/01/01,担当者A,食品,商品A,135
2008/01/01,担当者B,食品,商品A,90
2008/01/01,担当者C,食品,商品A,30
2008/01/01,担当者A,食品,商品B,175
2008/01/01,担当者A,食品,商品A,80
2008/01/01,担当者C,食品,商品B,30
2008/01/01,担当者A,食品,商品B,230
2008/01/01,担当者D,食品,商品C,330
2008/01/01,担当者A,食品,商品C,30
2008/01/03,担当者B,食品,商品A,30
2008/01/03,担当者A,食品,商品A,30
のようなデータとなっています。
これらの登録されたデータを日付期間を指定し、集計すると
2008/01/01～2008/01/03,担当者=6名（2008/01/01=4名、2008/01/03=2名）
食品,商品A,395
食品,商品B,430
食品,商品C,360
となり、これらの内容を本社の端末に入力します。
しかしながら、本社の端末に入力するには制約があります。
制約は、1画面あたり
1)　1画面あたり、最大15行の登録が可能（ただし在庫数は1行あたり最大99まで）
2)　1画面あたり、集計期間の担当者合計は最大99まで
3)　1画面あたり、15行分の商品数合計は最大999まで
4） 1画面毎に、入力した最大15行の商品数合計の入力をする必要があります。
となっています。
上記例では担当者数も商品大区分も商品小区分も少ないのですが、実際には約1000アイテムあり、本社への報告は集計期間の指示があった時に随時実行する必要があります。
つまり、上記例の場合で出力したいのは、
期間：2008/01/01～2008/01/03 集計期間の担当者合計=６名
食品,商品A,99
食品,商品A,99
食品,商品A,99
食品,商品A,98
食品,商品B,99
食品,商品B,99
食品,商品B,99
食品,商品B,99
食品,商品B,34
食品,商品C,99
食品,商品C,75　・・・15行には達していないが、在庫数最大値999を超えたので改ページ？改セクション？
→商品合計=999を表示

改ページ？改セクション？で
食品,商品C,24
食品,商品C,99
食品,商品C,63
→商品合計=186を表示
とする必要があります。
また、集計期間内に担当者の合計が99名を超えた場合の計算にも、どうすればいいのか・・・私の知識不足が多すぎます・・・

お分かり頂きにくいかも知れませんが、最終的に集計を上記のような仕様にして各行項目と合計値をプリントアウトしたいと思います。（今は適当な在庫数と行数で区切る手作業をしています）
手作業では余りにも大変なので、入力用の資料を作成できるように御教示いただけますよう宜しくお願い致します。

No.4 ベストアンサー 回答者： Dxak 回答日時： 2008/07/15 10:10

＃１です

私事で立て混んでて＾＾；；；遅くなって申し訳ありません

Dim SubSum As Long
Dim SubTol As Long
Dim usLine As Long

Private Sub グループヘッダー0_Format(Cancel As Integer, FormatCount As Integer)
SubSum = 0
End Sub

Private Sub ページフッターセクション_Format(Cancel As Integer, FormatCount As Integer)
Me.テキスト11 = SubTol
End Sub

Private Sub ページヘッダーセクション_Format(Cancel As Integer, FormatCount As Integer)
SubTol = 0
usLine = 0
End Sub

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)

Dim A As Long

If Me.在庫の合計 - (SubSum + 99) > 0 Then
A = 99
Else
A = Me.在庫の合計 - SubSum
End If
If SubTol + A > 999 Then
A = 999 - SubTol
End If

SubTol = SubTol + A
SubSum = SubSum + A
Me.テキスト3.Value = A
usLine = usLine + 1

If Me.在庫の合計 - SubSum <= 0 Then
Me.NextRecord = True
Else
Me.NextRecord = False
End If

If SubTol >= 999 Or usLine >= 15 Then
Me.改ページ8.Visible = True
Else
Me.改ページ8.Visible = False
End If

End Sub

前回のモジュールに追加して、
・９９９件で、改ページ
・１５行で、改ページ
して、あります
フッターにページ上の累計の「subTol」を代入して表示
増えたコントロールは、
詳細に
「改ページコントロール」：改ページ8
ページフッターに
「テキストボックス」：テキスト11
の２つ増やして、大体、条件どおり出ます

とりあえず、サンプルのデータでは、件数が少なくて、挙動確認が十分では、無い可能性がありますので、確認してみてください

この回答への補足

ありがとうございます。
ご連絡が遅くなり申し訳ございませんでした。出張から本日戻りましたので、
ご指導いただきました内容を今晩トライしてみて御報告申し上げます。
取り急ぎお礼まで....
m(__)m

補足日時：2008/07/19 22:24

この回答へのお礼

ご報告が遅くなりました。
テキスト3は各行の数量を表示するのだと解しますが、ご記載いただいたソースを使わせていただくと
元データ
区分1,区分2,在庫の合計
食品,商品A,395
食品,商品B,430
食品,商品C,360
に対して
区分1,区分2,テキスト3,在庫の合計
食品,商品A,35,395
食品,商品B,35,430
食品,商品C,-70,360
という結果になってしまいます。
ソースは、
Option Compare Database

Dim SubSum As Long
Dim SubTol As Long
Dim usLine As Long
Private Sub グループヘッダー0_Format(Cancel As Integer, FormatCount As Integer)

SubSum = 0

End Sub
Private Sub ページフッターセクション_Format(Cancel As Integer, FormatCount As Integer)

Me.FFF = SubTol

End Sub
Private Sub ページヘッダーセクション_Format(Cancel As Integer, FormatCount As Integer)

SubTol = 0
usLine = 0

End Sub
Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
Dim AA As Long


If Me.在庫の合計 - (SubSum + 99) > 0 Then
AA = 99

Else
AA = Me.在庫の合計 - SubSum

End If

If SubTol + AA > 999 Then
AA = 999 - SubTol
End If

SubTol = SubTol + AA
SubSum = SubSum + AA
Me.CCC = AA
usLine = usLine + 1

If Me.在庫の合計 - SubSum <= 0 Then

Me.NextRecord = True

Else

Me.NextRecord = False

End If

If SubTol >= 999 Or usLine >= 15 Then
Me.改ページ8.Visible = True
Else
Me.改ページ8.Visible = False
End If

End Sub
で、記載に間違いはないと思うのですが....
申し訳ありませんが、ご指導をお願い致します。

お礼日時：2008/07/27 00:32

No.7 回答者： Dxak 回答日時： 2008/07/31 12:23

> ところで、ご指導いただきました「と、実数に直して」のところは、

> どの部分をご指導いただいているのでしょうか？
いえ、＃５のお礼で書かれてるソースの通りですよ

現状、当方では
食品,商品A,395
食品,商品B,435
食品,商品C,360
のデータから

--- Page 1 ---
食品,商品A,99.75
食品,商品A,99.75
食品,商品A,99.75
食品,商品A,95.75
食品,商品B,99.75
食品,商品B,99.75
食品,商品B,99.75
食品,商品B,99.75
食品,商品B,36.00
食品,商品C,99.75
食品,商品C,70.00
--- Page 2 ---
食品,商品C,99.75
食品,商品C,90.50

のレポートが出力されてます
ちなみに、データは、どのように出力されてますか？
多分ですが、VBAソースではなく、コントロール類、レコードソース類で、差異が生じて、結果が違うとか、考えてるのですが・・・

この回答へのお礼

お礼が遅くなり申し訳ありませんでした。
助かりました。
ありがとうございました。

お礼日時：2008/10/08 18:53

No.6 回答者： Dxak 回答日時： 2008/07/31 09:38

> となると思いますが、ページが変わると前ページの残数を引きつかず

> 次のデータとなってしまいます。
の話だと、改ページコントロールの位置によって生じます

[AC97] レコードがない場合も用紙の最後まで罫線を出力する方法 - Microsoft
http://support.microsoft.com/default.aspx?scid=k …

これの応用で、現在、話を進めてるので、改ページコントロールの設置位置は、参考になると思いますが・・・
微妙な位置関係で、出たり、出なかったりと、苦慮することが多々あります
と、実数に直して、改ページコントロールの位置を微妙に横にずらしただけで、当方も、同じ症状を、生じてます
（改ページコントロールを触りなおして、プレビューしたら、直りましたが・・・）

この回答へのお礼

ご指導ありがとうございます。
改ページコントロールの位置をスペースが入らないように直したら症状が出ずに正確に表示できるようになりました。

ところで、ご指導いただきました「と、実数に直して」のところは、どの部分をご指導いただいているのでしょうか？何故かという事も含めてご指導頂けると助かります。
扱う数量が小数点2位までの数量が混在するものでソースを変更しないといけないと思ったもので・・・
本当にお手数をおかけしますが、宜しくお願い致します。

お礼日時：2008/07/31 11:42

No.5 回答者： Dxak 回答日時： 2008/07/28 09:38

＃１です

> テキスト3は各行の数量を表示するのだと解します
そうです
各行で１～９９を示す数量のテキストボックスですが・・・
非連結テキストボックスです

ソース自体に、相違があるようには、見えません

> 区分1,区分2,テキスト3,在庫の合計
> 食品,商品A,35,395
> 食品,商品B,35,430
> 食品,商品C,-70,360
> という結果になってしまいます。

１ページから、順番に見ていかないで、ページを飛ばすと・・・イベントが、うまく動作せず、集計結果が、おかしくなってる事が、あります
そう言う話は、ありませんか？
後、テキスト３の35、35、-70と言う話なのですが・・・９９は、幾つ出てますか？

Acc2002で、動作検証は、してますのが、Acc2000とは、さほど変わらないので、問題は、無いと思いますが・・・

この回答への補足

お手数をお掛けして申し訳ありません。
グループヘッダーの設定を間違っていました。
とりあえずは、同一データを一行最大９９で改行し表示できるようになりました。

しかし、例えば一行の元データが２００で
１４行目・・・９９
１５行目・・・９９
で改ページすると
１行目・・・２
となると思いますが、ページが変わると前ページの残数を引きつかず
次のデータとなってしまいます。

申し訳ありませんが、考えても自分では解決できません。
ソースは何の変更もしておりません。
ご指導を宜しくお願いいたします。

補足日時：2008/07/30 21:00

この回答へのお礼

すみません。記載漏れと使用している実数の説明不足がありましたので御詫びします。
実際に使用している数量は小数点２位までで、0.25きざみです。
そこで、実際にはソースを下記のように変更しています。
私なりに四苦八苦してみましたが、やはり2ページ目に渡るような場合には2ページ目1行目に端数が表示されません。
また、2ページ目の行数は15行とならず、14行となってしまっています。
しかし、２ページ目のsubTolは１ページ目の残数を加えた数値となっています。
次に、３ページ目以降も14行のままだったり、突然15行となったと思ったら、全くテキスト３だけ（計算の合わない数値）を1行目に表示して2行目から新しいレコードを元に表示を始めます。
本当に申し訳ありませんが、私には解決できそうにもありません。
どうか、どうか助けてください。お願いします。

Option Compare Database
Dim SubSum As Double
Dim SubTol As Double
Dim usLine As Integer

Private Sub グループヘッダー0_Format(Cancel As Integer, FormatCount As Integer)
SubSum = 0
End Sub

Private Sub ページフッターセクション_Format(Cancel As Integer, FormatCount As Integer)
Me.テキスト11 = SubTol
End Sub

Private Sub ページヘッダーセクション_Format(Cancel As Integer, FormatCount As Integer)
SubTol = 0
usLine = 0
End Sub

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)
Dim A As Double
If Me.在庫の合計 - (SubSum + 99.75) > 0 Then
A = 99.75
Else
A = Me.在庫の合計 - SubSum
End If

If SubTol + A > 999.75 Then
A = 999.75 - SubTol
End If

SubTol = SubTol + A
SubSum = SubSum + A
Me.テキスト3.Value = A
usLine = usLine + 1

If Me.在庫の合計 - SubSum <= 0 Then
Me.NextRecord = True
Else
Me.NextRecord = False
End If

If SubTol >= 999.75 Or usLine >= 15 Then
Me.改ページ8.Visible = True
Else
Me.改ページ8.Visible = False
End If

End Sub

お礼日時：2008/07/31 00:43

No.3 回答者： boc-ian 回答日時： 2008/07/12 03:24

横から失礼します。

こんなアイデアはどうでしょう。
(1) テーブルに『ページ数』というフィールドを作っておきます。

(2)　次のようなVBAのプロシージャで、『ページ数』を埋めていきます。
-1　対象期間のレコードセットを作る。
-2　頭から1レコードずつチェックして、999人とか、15行とかの与えられた条件を満たすまでレコードを拾い上げる。
-3　拾い上げると同時に、『ページ数』に『1』を埋めていく。
-4　1ページの条件が満たされたら、次のレコードから『ページ数』に『2』を埋めていく。
-5　以下、レコードがなくなるまで同様に続ける。
例えばこんなイメージです。
Sub X()
Dim Cnxn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim PageNo as Integer
Set Cnxn = CurrentProject.Connection
rst.Open "SELECT * FROM Table WHERE BETWEEN #いつから# AND #いつまで#".....
With rst
PageNo=1
While Not(.EOF)
与えられた条件に関する変数の初期化
最初のレコードの、与えられた条件に関する変数の計算
While Not(.EOF) AND (Not(与えられた条件1) AND Not (与えられた条件2) AND .....)
![ページ数]=PageNo
.Update
.MoveNext
与えられた条件に関する変数の計算
Wend
PageNo=PageNo+1
Wend
End With
rst.Close
Set rst=Nothing
Set Cnxn=Nothing
End Sub

(3)　親レポート／フォームは、ページ数のみをリストアップした集計クエリーをもとに作ります。ただし、空欄(あるいはヌル)のレコードは除外してください。
SELECT ページ数 FROM Table GROUP BY ページ数 HAVING nz(ページ数,"")<>"" ORDER BY ページ数
みたいな感じです。
フィールド名、テーブル名を変えてSQLに貼り付け、クエリデザインビューに変えてもらえば、お伝えしたいクエリをごらんいただけます。

(4)　サブレポート／フォームは、目的のレコードで、『ページ数』でリンクさせます。ページ数が埋まっていないものは、自動的に対象外になります。

サブレボート／フォームの集計の仕方は、いいですよね？

埋めた『ページ数』は、プロシージャの最後で消してもいいし、このプロシージャを次に実行する前に消してもいいし、都合のいいほうを選べばいいと思います。
元のテーブルにフィールドを追加したり、ページ数を書き込んだりするのが都合が悪ければ、対象期間のレコードを、別の仮のテーブルに書き出してもいいですね。

美しくない方法かもしれませんが、ご参考まで。

この回答への補足

ありがとうございます。
初心者の私には難しいと感じる次第です。
それと、元のテーブルにフィールドの追加ができないので・・・どうしたらよいものか・・・
私の理解力不足だと思うのですが、日々の集計、グループ化～指定期間の集計など、どのように進めていくべきか・・・
目的の作業が完遂できる手法を、できれば初心者の私に御指導いただければ幸いです。
申し訳ございません。

補足日時：2008/07/12 18:31

この回答へのお礼

お礼が遅くなり申し訳ありませんでした。
助かりました。
ありがとうございました。

お礼日時：2008/10/08 18:54

No.2 回答者： Dxak 回答日時： 2008/07/10 11:59

＃１です

> 現時点では1日に99名を超えることは無いのですが、
> 近々に超える予定があります。
そう来ましたか・・・どう別けるのが良いか？検討が必要になってきます
理想として・・・

９９名を超えないよう、グループ分けしてしまうのが、最初かな？
> 2008/01/01～2008/01/03,担当者=6名
>（2008/01/01=4名、2008/01/03=2名）
> 食品,商品A,395
> 食品,商品B,430
> 食品,商品C,360
の部分を３人を超えないとして、仮定して・・・演算するとして

クエリで、Groupを付加して、３人で制限をかけてみた場合

Group, 日付, 担当者, 商品大区分, 商品小区分, 在庫
1, 2008/01/01, 担当者A, 食品, 商品C, 30
1, 2008/01/01, 担当者D, 食品, 商品C, 330
1, 2008/01/01, 担当者A, 食品, 商品B, 230
1, 2008/01/01, 担当者A, 食品, 商品A, 80
1, 2008/01/01, 担当者A, 食品, 商品B, 175
1, 2008/01/01, 担当者B, 食品, 商品A, 90
1, 2008/01/01, 担当者A, 食品, 商品A, 135
2, 2008/01/03, 担当者A, 食品, 商品A, 30
2, 2008/01/03, 担当者B, 食品, 商品A, 30
2, 2008/01/01, 担当者C, 食品, 商品B, 30
2, 2008/01/01, 担当者C, 食品, 商品A, 30

を集計して

Group, 商品大区分, 商品小区分, 在庫の合計
1, 食品, 商品A, 305
1, 食品, 商品B, 405
1, 食品, 商品C, 360
2, 食品, 商品A, 90
2, 食品, 商品B, 30

Group1 : 2008/01/01 ～ 2008/01/01, 担当者A,担当者B,担当者D
Group2 : 2008/01/01 ～ 2008/01/03, 担当者A,担当者B,担当者C

で、別けてあります
一応、クエリで動作させてあるけど・・主キーを要しないテーブルでは、別け方も難しいので、複雑怪奇なクエリになる上に、グループごとの在庫数が均等になってこない、条件によっては、３人のつもりが４人になってたり・・・色々難しいみたいです
まずは、９９人の話をクエリ側で、手を打ちたいところですが・・・
どう別けるか？検討していく必要がありますが、質問者さまは、どういうルールで別けたい？
例の話で言えば、日付、担当者で、在庫数を集計し、在庫数が多い順に順番を振り、３人でごとに、グループ番号を振りなおしたと言うクエリで・・・日付、在庫数で、順番を振った場合、同一になったら、順番が重複する事象が生じて、計算が成り立たなくなる＾＾；；；
それを集計もとのテーブルと連結しなおし、集計したと言う話
説明だけでも、結構複雑なのよ＾＾；；；
（クエリは、３重にクエリを介してる上に、サブクエリで他にも集計してたり・・・）

グループの別け方、どうするか？が、課題になってくるけど・・・
他のところは、レポートで細工、可能だね
担当者数の話は、集計後、分割する方法が無いから、クエリ側で考えるしかないけどね

この回答への補足

たいへんお世話をお掛けして申し訳ありません。
どう考えても人数が増えてしまうことを想定してしまうと複雑怪奇な状態になってしまいそうなので、本社に交渉してみました。
なにせ、集計期間が1週間程度で指示があると行数だけでなく、人数は間違いなく９９名を超えるので、1画面あたりの制限は外してもらえないものの、入力時の任意操作で許してもらうようにしました。
具体的には、
1日目の担当者ABCDE・・・・・で65名
2日目の担当者ABCDEFG・・・・で75名
・・・・7日目の担当者ABCDEFGで75名
合計400名だった場合に、15行分の在庫数は最大999で10ページの出力
となってしまったとすると、
1画面あたりの担当者を均等割りして、40名と入力しても良いし、任意の担当者数をそれぞれ10ページに入力して、合計が400名となるよう、つじつまが合えば良いとして頂きました。

したがいまして、合計人数（1日あたり重複する担当者は必ず1名とする）さえ表示できれば、人数面は解決という事で御指導いただけますでしょうか・・・
本当に申し訳ございません。
愚痴ながら、本社から集計入力指示があると間違いなく家に帰れない日が最低2日に渡ってしまいます。
御指導頂き、欲しいデータが出力できれば、非常に助かりますので何卒宜しくお願い致します。

補足日時：2008/07/11 00:23

この回答へのお礼

お礼が遅くなり申し訳ありませんでした。
助かりました。
ありがとうございました。

お礼日時：2008/10/08 18:55

No.1 回答者： Dxak 回答日時： 2008/07/09 19:19

色々、考えてみたけど・・・クエリを細工するより、レポートで細工した方が、早いかな？

但し
> 2)　1画面あたり、集計期間の担当者合計は最大99まで
の辺りが・・・集計期間の調整で、何とかなるものなのか？
要は、１日で９９名を超えると・・・集計期間を弄って、９９人以下に調整と言う訳にも行かず、担当者ごとに、どう分けるか？悩まざる終えない

レポートは、
> 食品,商品A,395
> 食品,商品B,430
> 食品,商品C,360
をレコードソースに、「Me.NextRecord」のTrue/Faluseを操作して、行数を増やす（増やしたように見せかける）と、言う話ね

例えば、例の話だけで言えば・・・
在庫の合計（これは、395,430,360の話、非表示でＯＫ）
在庫表示（これは、モジュールから操作するので非連結）
商品小区分のグループヘッダを「はい」に変更しておいて、
モジュールを

Dim SubSum As Long

Private Sub グループヘッダー0_Format(Cancel As Integer, FormatCount As Integer)
SubSum = 0
End Sub

Private Sub 詳細_Format(Cancel As Integer, FormatCount As Integer)

SubSum = SubSum + 99
If Me.[在庫の合計] - SubSum <= 0 Then
Me.[在庫表示].Value = Me.[在庫の合計] - SubSum + 99
Me.NextRecord = True
Else
Me.[在庫表示].Value = 99
Me.NextRecord = False
End If

End Sub

と、すれば・・・在庫数９９個毎に表示は出せる
後は、改ページコントロールを設置、在庫合計９９９個を越える前に改ページを動作させる・・・まだ、モジュールを工夫していくのは、あるけど・・・

ＤＡＯ等で、レポート用テープルを作って、レポートでの操作を楽にすると、言うのも手はあるけど・・・改ページコントロールを使用して、動かさないと、無理がありそうだから、ついでにね

で、話を戻して、担当数の話は？1日で９９人超えること、ありそう？

この回答への補足

早々にありがとうございます。
現時点では1日に99名を超えることは無いのですが、近々に超える予定があります。
それと、集計期間が2日以上に渡れば、現時点でも99名を超えてしまっています。本社は1日だけの集計を今までは求めて来たことは無いのですが、今後はどうか不明な状況です・・・
何卒ご指導を宜しくお願いいたします。

補足日時：2008/07/09 20:01

この回答へのお礼

お礼が遅くなり申し訳ありませんでした。
助かりました。
ありがとうございました。

お礼日時：2008/10/08 18:55