Access2003 フォームの合計の出し方

お世話になっております。

以下のような３重の構造になったフォームがあります。

・受注フォーム
・商品フォーム（子フォーム）
・オプションフォーム（孫フォーム）（帳票フォーム）

ひとつの受注番号で複数の商品を受注します。
ひとつの商品には複数のオプションがあります。

それぞれのフォームが伝票コード、明細コードでリレーションされています。

オプションの合計はオプションフォームのフッタ部分のテキストボックスのコントロールソースに=Sun(オプション金額)のようにして求めています。

商品（ひとつ）＋オプション（複数）の合計は商品フォームのフッタ部分のテキストボックスのコントロールソースに数式を書いて求めています。

ここまでは簡単にできるのですが、受注コードごとの合計、つまり商品（複数）＋オプション（さらに複数）を表示させることができません。

数式だけでは無理なようですのでクエリを使うと思うのですが、３重になっているためかうまく演算してくれません。

商品フォームとオプションフォームをデータシートにすれば受注フォームにすべての数値が表示されるため合計は出せますが、使い勝手が悪いので避けたいです。

何か解決のヒントでもいただけたら幸いです。よろしくお願いいたします。

No.6 ベストアンサー 回答者： O_cyan 回答日時： 2006/03/31 19:04

いろいろ考えましたがSQLでレコードセットを取得するかですね。

後は良い案が浮かびません。すいません。
オプションフォームのレコード移動時のイベントで

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = ""
strSQL = strSQL & "SELECT オプションテーブル.商品明細No, Sum(オプションテーブル.金額) AS OP金額, 受注テーブル.受注No "
strSQL = strSQL & "FROM 受注テーブル RIGHT JOIN (オプションテーブル INNER JOIN 商品明細テーブル ON オプションテーブル.商品明細No=商品明細テーブル.商品明細No) "
strSQL = strSQL & "ON 受注テーブル.受注No=商品明細テーブル.受注No GROUP BY オプションテーブル.商品明細No, 受注テーブル.受注No "
strSQL = strSQL & "HAVING (((受注テーブル.受注No)=Forms!受注フォーム!受注No));"

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic

Forms!受注フォーム!オプション合計.ControlSource = "OP金額"
Forms![受注フォーム].Requery

これでOP金額に合計を取得出来ると思うのですが・・。検証していません。
オプション合計のコントロールにOP合計をセットして受注フォームをForms![受注フォーム].Requeryすれば行けるかも。
商品フォームとオプションフォームが複数の商品明細Noを表示(入力)できる仕様なら簡単なんですけどね。

この回答へのお礼

何度もありがとうございます。
作業をしている途中でデータベースが壊れてしまい、最後までできませんでした。
最初から作り直すつもりですので、教えていただいた内容が実現できるようにがんばりたいと思います。
本当にありがとうございました。

お礼日時：2006/04/06 00:15

No.5 回答者： O_cyan 回答日時： 2006/03/24 18:17

>テーブルに金額があったほうがいいのでしょうか

商品・オプションの金額に変動が無いようなら必要ありません。
金額に変動がある場合はその時点の金額の把握が必要になるので無いと現時点の金額でしか演算等できなくなるなってしまうのでそういう事がなければ必要はありません。
>オプションクエリには受注Noが関係するものがありません
受注テーブル.受注No―商品明細テーブル.受注No
商品明細テーブル.商品明細No―オプションテーブル.商品明細No
のリレーションという事ですね。
オプションクエリは
受注Tbl.受注No―商品明細Tbl.受注No：商品明細Tbl.商品明細No―オプションテーブル.商品明細Noの結合でフィールドは受注Tbl.受注No：オプションテーブル.金額で
受注Tbl.受注Noをグループ化・オプションテーブル.金額を合計で求めているので良いのですよね。
上記の結合でフィールドを受注Tbl.受注No：商品明細Tbl.金額：オプションテーブル.金額でも出ますが。
オプションの合計のどの辺で行き詰っているのでしょうか。

この回答への補足

ありがとうございます。
金額は変動しませんのでそのままでいきたいと思います。
リレーションはそのとおりです。

オプションクエリはオプションテーブルとオプションマスターテーブルのみです。
オプションテーブルに数量があり、オプションマスターテーブルに単価がありますので合計を計算するクエリとなっています。

そこに商品テーブルを追加すると、
あいまいな外部結合が含まれているので、SQLステートメントを実行できません・・・
というエラーになります。

商品クエリを追加しても、
指定されたフィールド”乗率”がSQLステートメントのFROM句にある複数のテーブルを参照しました。
というエラーになります。

受注テーブルは追加できますが、これだけでは結合できませんので意味がないと思います。このようにこれ以上は追加できないようです。

DSum関数を使ってできれば一番いいのですが、条件が[受注No] = [受注No]だけでは不足しているようですので、これがわかればできそうです。
受注フォームすべての合計は計算していますので、商品クエリの抽出条件のようなものか何かで受注Noで限定できれば一番いいと思います。

このような状態で悶々としております。よろしくお願いいたします。

補足日時：2006/03/24 19:38

No.4 回答者： O_cyan 回答日時： 2006/03/24 12:55

>受注フォームで新規入力ができなくなりました

>SQL・・・クエリを追加・・しなれれば新規入力ができます・・ここに何か問題があると思います。

まことに申し訳ありません。うっかりしてました。
受注フォームの元になるテーブルのレコードを移動できなくなってしまいます。一覧フォームなどからそのレコードをフォーム上に表示して修正のみできる方法でした。
前回からずっと「クエリで処理してるんだ」と引きずっていたので・・m(_ _)m
本当にすいません。
もっと簡単に処理することを前提にしていれば良かったのにと反省しきりです。
簡単に処理できます。DSumを使ってください。
受注フォームの合計のコントロールソースに
=DSum("[金額]","商品テーブル","[受注No] = [受注No]")+DSum("[金額]","オプションテーブル","[受注No] = [受注No]")
とすれば商品テーブルの金額とオプションテーブルの金額を受注Noで合計できるのを忘れてました。
商品テーブル・オプションテーブル・金額・受注Noは使用しているテーブル名・フィールド名に変更してください。
オプションフォーム等のレコードの移動時イベントでのForms![受注フォーム].Requeryは必要です。
余分なことをさせてすいませんでした。

この回答への補足

何度もありがとうございます。
商品テーブルとオプションテーブルには金額がありませんので、商品クエリとオプションクエリとしてやってみたところ、フォーム自体の全合計が表示されました。
（テーブルに金額があったほうがいいのでしょうか？）
受注フォームのすべての商品とオプションの合計です。

そこで、商品クエリの受注Noの抽出条件に[Forms]![受注フォーム]![受注No]を入れてみたところ商品合計は受注Noごとに表示できるようになりました。
ただこれはあまり良い方法ではないように思います。

オプションクエリには受注Noが関係するものがありませんので（受注明細Noがあります）適当にテーブルやクエリを入れていろいろ試みましたがダメでした。
やはりオプションの合計でつまずいてしまいました。

このような状況ですので何度も申し訳ございませんがお願いしたいと思います。
よろしくお願いいたします。

補足日時：2006/03/24 15:46

No.3 回答者： O_cyan 回答日時： 2006/03/24 10:19

>受注フォームには何も表示されず、フォームが背景だけに

オプション合計クエリには抽出条件は入っているでしょうか？例えばフォームのコントロールを参照するような記述とか入っていませんでしょうか？
SQLステートメントにネストしたクエリに[Forms]![受注フォーム]![受注No]等のフォームのコントロールを参照するような記述がある場合フォームのコントロールが表示されなくなってしまいます。
この辺の確認をしてみてください。
クエリがオプションの合計をグループ化して受注Noで合計を出すのみ(抽出条件はなし)であればSQLにネストして受注No同士をSQL上で結合すれば回避できるはずです。

この回答への補足

お世話になっております。何度もありがとうございます。

オプション合計クエリには抽出条件[Forms]![受注フォーム]![受注No]が入っていましたので、これを削除したところ表示されました。
このあたりの意味はよくわかりませんが、これから勉強したいと思います。

それから問題が発生しまして、受注フォームで新規入力ができなくなりました。
新規入力ボタンがグレーアウトしていますので、コマンドボタンで新規入力を作ってみたところ、”指定したレコードに移動できません。”と表示されました。

SQLステートメントにオプション合計クエリを追加しなければ新規入力ができますので、ここに何か問題があると思います。

原因がわかりましたら教えていただければ幸いです。
何度もすみませんがよろしくお願いいたします。

補足日時：2006/03/24 11:22

No.2 回答者： O_cyan 回答日時： 2006/03/23 11:10

>追加しようとすると、受注フォームのほかの部分がすべてエラー

フォームのプロパティでレコードソースのボタンをクリックしクエリビルダでSQLステートメントを起動して受注フォームの元になっているテーブルの*を選択しステートメントのフィールドに追加します。
SQLステートメント上で右クリックでテーブルの表示を選択。商品金額合計とオプション金額合計をクエリ内から選び追加ボタンでSQLステートメントに追加します。
商品金額合計とオプション金額合計をフィールドに追加します。受注フォームの元になるテーブルと追加したクエリの受注No(キー)同士をドラッグして結合します。(商品金額合計・オプション金額合計のクエリには抽出条件などは入れないでください。クエリ上で受注フォームのキーと結合するため)
これで受注フォームの各コントロールはエラーにならないはずです。受注フォーム上に商品金額合計とオプション金額合計のコントロールを配置すれば出来るはずなのですが。

この回答への補足

何度もありがとうございます。
そのとおりにやってみましたが受注フォームには何も表示されず、フォームが背景だけになってしまいました。
SQLステートメントにオプション合計クエリを追加してビューで切り替えるとデータが何も表示されませんのでこれが原因と思われます。
（ためしに違うクエリを追加してみると、データがいろいろと表示されます。）
ご指示の内容と違うのは、オプション合計クエリを追加した時点ですでに受注Noで結合されていることだけです。あとはそのとおりにやりました。

金額を求める商品クエリとオプションクエリは別々にありまして、それぞれが別のコードでリレーションされています。
（単価と数量で合計を求めるため、クエリで計算しています。）

今回の課題であったオプションの合計を求めるオプション合計クエリには受注テーブルと商品クエリ、オプションクエリが選んであり（結合してあります）、フィールドには受注テーブルの受注Noとオプションクエリのオプション金額が選んであります。
これをグループ化してオプション金額の合計を求めています。

原因がわかりましたら教えていただければ幸いです。よろしくお願いいたします。

補足日時：2006/03/24 00:56

No.1 回答者： O_cyan 回答日時： 2006/03/22 13:00

商品フォームには１つの商品しか表示しないという事で良いのでしょうか？商品毎に商品フォーム・オプションフォームに入力という事ですね。

商品フォーム・オプションフォームに一つではなく全て入力できれば各フォームの合計しているコントロールを
=[受注フォーム]![商品フォーム]![合計]+
[受注フォーム]![商品フォーム]![オプションフォーム]![合計]
で参照するだけで済むのに処理が多くなると大変ですね。

例えばクエリで受注テーブルの受注Noと商品明細テーブル・オプションテーブルの受注Noでリレーションしフィールドは受注テーブル.受注No・商品明細テーブル.金額・オプションテーブル.金額の３つをフィールドとします。
受注テーブル.受注Noの抽出条件に
[Forms]![受注フォーム]![受注No]　として受注フォーム上の受注Noを抽出条件に指定しグループ化して商品明細テーブル.金額・オプションテーブル.金額を合計にすれば各合計金額が演算できますよね。
このクエリを元に各合計金額を表示するコントロールを受注フォームに配置。
受注フォームのプロパティを開きレコードソースにSQLステートメントで追加し各合計金額のフィールドを追加。
商品フォームでレコードを追加し必ずオプションフォームで入力があればオプションフォームでレコードの移動時などのイベントでForms![受注フォーム].Requeryとして再クエリをする。この時点で商品フォーム上のレコードが商品明細テーブルに保存されていないようだと商品フォームでもレコードを保存するDoCmd.RunCommand acCmdSaveRecordとRequeryが必要になります。
Requeryのタイミングは色々試してみてください。

この回答への補足

お世話になっております。
以前に似たような質問をしたのですが、そのときは結局うまくいかずにずっと悩んでおりました。
おかげさまで課題であったオプションの合計を計算することができました。
ありがとうございました。

あともう少しなのですが、オプションの合計をテキストボックスに表示させようと思います。その次の、

> 受注フォームのプロパティを開きレコードソースにSQLステートメントで追加し各合計金額のフィールドを追加。

この部分に何かを追加しようとすると、受注フォームのほかの部分がすべてエラーになったり、受注フォームのすべてのフィールドを追加してからオプションの合計らしいものを追加しようとしても表示されなかったり、うまくできません。

いろいろ調べてみましたが、具体的に何をどうすればいいかがわかりませんでした。
すみませんが、教えていただければありがたいです。
よろしくお願いいたします。

補足日時：2006/03/22 22:06