Access (一般機能) |
![]() ![]() |
(Windows 10 Pro : Access 2016)
クエリデザインで計算式の作り方
投稿日時: 20/09/22 19:43:23
投稿者: 水の味
|
---|---|
お世話になります
|
![]() |
投稿日時: 20/09/22 23:08:14
投稿者: よろずや
|
---|---|
水の味 さんの引用: クエリ内に計算式が多数ある場合に、その様な現象が出ることがあります。 どういう計算式が使われているのかが判ると解決法を示しやすいのですが。 クエリをSQLビューで開いて出てくるSQL文を提示してください。 |
![]() |
投稿日時: 20/09/23 10:27:01
投稿者: 水の味
|
---|---|
よろずやさん
|
![]() |
投稿日時: 20/09/23 12:08:42
投稿者: Suzu
|
---|---|
引用: 多分、表示されないのは、「仕入金額」フィールドでは? 演算フィールドの数式の一部として、別の演算フィールドの結果を入れたい場合 通常 A:B*C D:A*10 の様に、数式の一部に、演算フィールドで指定した別名を指定する書き方で可能です。 が、数式が複雑になってくると、今回の様に表示されない事が起きます。 その場合には、別名を指定するのではなく、数式の中身をそのまま指定します。 A:B*C D:B*C*10 今回の「仕入金額」の数式を 仕入金額:Round(([発注数の合計]-[在庫])*0.1)*10*[仕入単価] としてみてください。 複雑とは一概に言えませんが、今回の様に集計クエリの場合には発生する事が多いです。 今回のSQLを拝見すると、 「固有の値」:はい の上に、更に 集計クエリとして グループ化しています。 仕入品番について、 ・在庫データテーブル ・工程テーブルマスター それぞれで1件づつなのであれば、 それは必要ないことになります。 SELECT 在庫データテーブル.注文番号, KokyakuA.顧客コード, 工程テーブルマスター.会社名, KokyakuA.担当者名, 工程テーブルマスター.区分, 在庫データテーブル.仕入品番, 在庫データテーブル.発注数の合計, 在庫データテーブル.発注数単位, Round(([発注数の合計]-[在庫])*0.1)*10 AS 発注数, 在庫データテーブル.納期, 在庫データテーブル.在庫, 在庫データテーブル.発注取消, 在庫データテーブル.備考, 在庫データテーブル.入力日, Mid$([納期],5,2) AS 月分, 工程テーブルマスター.仕入単価, Round(([発注数の合計]-[在庫])*0.1)*10*[仕入単価] AS 仕入金額 FROM 在庫データテーブル LEFT JOIN ( 工程テーブルマスター INNER JOIN KokyakuA ON 工程テーブルマスター.会社名 = KokyakuA.会社名 ) ON 在庫データテーブル.仕入品番 = 工程テーブルマスター.仕入品番 WHERE ( (在庫データテーブル.注文番号>=92) AND (KokyakuA.顧客コード =101 Or KokyakuA.顧客コード=320 Or KokyakuA.顧客コード=118) AND (工程テーブルマスター.区分="発注先") AND (Round([発注数の合計]-[在庫]*0.1)*10)>5) AND (在庫データテーブル.納期=20201001) AND (在庫データテーブル.発注取消 Is Null) ) ; として確認してみてください。 |
![]() |
投稿日時: 20/09/23 14:42:32
投稿者: 水の味
|
---|---|
よろずやさん
|
![]() |
投稿日時: 20/09/23 18:40:19
投稿者: 水の味
|
---|---|
SUZUさん名前を間違えてしまい失礼しました。
|
![]() |
投稿日時: 20/09/23 21:17:12
投稿者: よろずや
|
---|---|
提示されたクエリの他に、レポートの中でクエリを作っていませんか? |
![]() |
投稿日時: 20/09/24 09:55:45
投稿者: 水の味
|
---|---|
よろずやさん
|
![]() |
投稿日時: 20/09/24 10:08:13
投稿者: Suzu
|
---|---|
LEFT JOIN は 問題の直接の原因ではありません。
引用: これは、クエリSQL の「発注数」の内容を説明する為に フィールドを変えて提示したという認識で良いでしょうか? また、印刷プレビューで表示されないコントロールのコントロールソースが、 「発注数」になっている事を確認ください。 2. レポートをプレビューした際 パラメーターの入力ダイアログ「発注数」が表示されていない事を確認ください。 (もし表示されているなら、ダイアログに対し、何も入力せず 「OK」で進めていませんか?) 3. 提示頂いたクエリを実行し、求める結果が問題なく表示される事を確認ください。 (当方 Access2013 にて実行すると、 パラメーターの入力ダイアログ「発注数」が表示されてしまいます。) 4. 上記パラメーターが表示されてもされなくとも、 新規クエリを作成し SQLを 下記として保存し、そのクエリをレポートのレコードソースとして 実行してみてください。 SELECT DISTINCT 在庫データテーブル.注文番号, KokyakuA.顧客コード, 工程テーブルマスター.会社名, KokyakuA.担当者名, 工程テーブルマスター.区分, 在庫データテーブル.仕入品番, 在庫データテーブル.発注数の合計, 在庫データテーブル.発注数単位, Round(([発注数の合計]-[在庫])*0.1)*10 AS 発注数, 在庫データテーブル.納期, 在庫データテーブル.在庫, 在庫データテーブル.発注取消, 在庫データテーブル.備考, 在庫データテーブル.入力日, Mid$([納期],5,2) AS 月分, 工程テーブルマスター.仕入単価, Round(([発注数の合計]-[在庫])*0.1)*10*[仕入単価] AS 仕入金額 FROM 在庫データテーブル INNER JOIN (工程テーブルマスター INNER JOIN KokyakuA ON 工程テーブルマスター.会社名 = KokyakuA.会社名) ON 在庫データテーブル.仕入品番 = 工程テーブルマスター.仕入品番 GROUP BY 在庫データテーブル.注文番号, KokyakuA.顧客コード, 工程テーブルマスター.会社名, KokyakuA.担当者名, 工程テーブルマスター.区分, 在庫データテーブル.仕入品番, 在庫データテーブル.発注数の合計, 在庫データテーブル.発注数単位, 在庫データテーブル.納期, 在庫データテーブル.在庫, 在庫データテーブル.発注取消, 在庫データテーブル.備考, 在庫データテーブル.入力日, Mid$([納期],5,2), 工程テーブルマスター.仕入単価, Round(([発注数の合計]-[在庫])*0.1)*10*[仕入単価] HAVING (((在庫データテーブル.注文番号)>=92) AND ((KokyakuA.顧客コード)=101 Or (KokyakuA.顧客コード)=320 Or (KokyakuA.顧客コード)=118) AND ((工程テーブルマスター.区分)="発注先") AND ((Round(([発注数の合計]-[在庫])*0.1)*10)>5) AND ((在庫データテーブル.納期)=20201001) AND ((在庫データテーブル.発注取消) Is Null)); |
![]() |
投稿日時: 20/09/24 10:34:50
投稿者: Suzu
|
---|---|
原因と解決は、先に述べた通りです。
引用: ------------------------------------------------------------------------------- ここから先は 間接的な部分になります。 間接的な問題点として クエリを「集計クエリ」「固有クエリ」にしている点です。 SUMや、COUNT等の集計を行っているフィールドがありません。 なぜ「集計クエリ」/「固有クエリ」にしているのでしょうか? 「在庫データテーブル」「工程テーブルマスター」「KokyakuA」各テーブルの主キーはどうなっていますか? 固有でないデータが存在しうるのでしょうか? (在庫データテーブル.発注数の合計 「の合計」は集計クエリの計算結果のデフォルトの名称である事が 引っ掛かっています) レコードが重複する「可能性」を否定する為にそのクエリにしているのであれば テーブル設計に問題がある可能性があります。 重複しないのであれば SELECT 在庫データテーブル.注文番号, KokyakuA.顧客コード, 工程テーブルマスター.会社名, KokyakuA.担当者名, 工程テーブルマスター.区分, 在庫データテーブル.仕入品番, 在庫データテーブル.発注数の合計, 在庫データテーブル.発注数単位, Round(([発注数の合計]-[在庫])*0.1)*10 AS 発注数, 在庫データテーブル.納期, 在庫データテーブル.在庫, 在庫データテーブル.発注取消, 在庫データテーブル.備考, 在庫データテーブル.入力日, Mid$([納期],5,2) AS 月分, 工程テーブルマスター.仕入単価, [発注数]*[仕入単価] AS 仕入金額 FROM 在庫データテーブル INNER JOIN (工程テーブルマスター INNER JOIN KokyakuA ON 工程テーブルマスター.会社名 = KokyakuA.会社名) ON 在庫データテーブル.仕入品番 = 工程テーブルマスター.仕入品番 WHERE (((在庫データテーブル.注文番号)>=92) AND ((KokyakuA.顧客コード)=101 Or (KokyakuA.顧客コード)=320 Or (KokyakuA.顧客コード)=118) AND ((工程テーブルマスター.区分)="発注先") AND ((Round(([発注数の合計]-[在庫])*0.1)*10)>5) AND ((在庫データテーブル.納期)=20201001) AND ((在庫データテーブル.発注取消) Is Null)); で良く [発注数]*[仕入単価] AS 仕入金額 が使用でき、 集計クエリ/固有クエリ の処理を行わない分 クエリを高速に処理する事が可能となります。 多分、重複させない為の技法を検索され導入したと思います。 初めのうちは 自信が無いので 求める結果を得る為に いろいろ検索し 取り入れる姿勢はとても良いと思います。 今回であれば、「なぜ 重複するのか」、「もともとの部分で重複させない為にどうするか」 の様に踏み込んで行くとスキルアップに繋がると思います。 がんばってください。 |
![]() |
投稿日時: 20/09/24 17:35:45
投稿者: 水の味
|
---|---|
お世話になります。
Suzu さんの引用: |
![]() |
投稿日時: 20/09/24 18:00:04
投稿者: 水の味
|
---|---|
お世話になります。
Suzu さんの引用: |
![]() |
投稿日時: 20/09/25 09:57:07
投稿者: Suzu
|
---|---|
引用: パラメーターの入力ダイアログは、 ユーザーが流動的に値を与えてその値をクエリ等内の計算式の変数として扱い 計算結果に反映する機能があり、そのパラメーターを指定してください という意味で出てきます。 https://www.moug.net/tech/acopr/0040008.html https://www.moug.net/tech/acopr/0040009.html 今回は、 SELECT DISTINCT 在庫データテーブル.注文番号, ・・・(中略)・・・[発注数]*[仕入単価] AS 仕入金額 FROM 在庫データテーブル ・・・(中略)・・・ GROUP BY 在庫データテーブル.注文番号, ・・・(中略)・・・ [発注数]*[仕入単価] HAVING ・・・(中略)・・・ の「発注数」フィールドを見つける事が出来ないためにパラメーターだと判断し ユーザーに値の入力を求めてきています。 ですので、そのダイアログに何も入力せずに「OK」だけ押すと 【空白】*[仕入単価] が計算され、結果として【空白】が返る事となります。 引用: 計算式内にて「発注数」を使わないクエリにすれば対応できる事が判りましたよね? 安直には、クエリをその様に変えれば良い事になります。 あくまでも「安直」にです。 厳しい言い方になります。 今回、原因その要因も説明させて頂きました。 手がかりとなるキーワード「集計クエリ」「固有クエリ」や参考となるSQLも提示しております。 初めは見様見真似大いに結構と思います。 SQL二つ提示しています。実行した結果を比較されましたか? それを言われたまま実行し結果を報告するだけではスキルアップは望めません。 スキルアップを目指すのであれば、何故そうなるのか?判らない事を調べる事が必要になります。 希望の結果が得られれば良いのか、そこにスキルアップも求めるのか、水の味さん次第です。 |
![]() |
投稿日時: 20/09/25 10:24:46
投稿者: 水の味
|
---|---|
SUZUさん
|