質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.35%
Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

解決済

1回答

355閲覧

(再質問)Accessクエリの複数レコード作成について

tailtera22

総合スコア3

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

0クリップ

投稿2020/09/18 05:03

すみません、以下のページで問題は解決していたのですが
https://teratail.com/questions/292474#reply-413763
新たな壁にぶち当たりました。

商品ID 商品名 商品価格 数量  Ord 0001 シャツ 3000 1    1 0001 送料   330   1    2 0002 靴    5000 1    1 0003 スラックス  8000 1    1 0003 スラックス 8000 2    1 0003 送料    330   1    2 0003 送料    330   2    2

0003のような特殊なパターンがあり
スラックスの重複は残しつつ
一番下の送料レコードの重複分だけを除外したいのですが、どうやってもうまくいきません。

固有の値を設定して重複を無くそうとしても、数量が違うので当然まとまりませんし
そもそも集計クエリでスラックスを1レコードにまとめることは
出力テーブルの読込先である外部ソフトのフォーマット上NGなので
送料の最小を取っても、当然スラックスに影響してしまい、
抽出条件で何とかならないか足掻いてみましたが、圧倒的な脳みそ不足でどうにもなりませんでした。

これはもう根本的に元のクエリを変えなければならないのでしょうか?

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2020/09/18 05:13

元のデータと、希望の結果も示してください。
tailtera22

2020/09/18 05:38

sazi様 すみません、希望している結果は下の通りです。 商品ID 商品名 商品価格 数量  Ord 0001 シャツ 3000 1    1 0001 送料   330   1    2 0002 靴    5000 1    1 0003 スラックス  8000 1    1 0003 スラックス 8000 2    1 0003 送料    330   1    2 数量が小さいほうの送料レコードを残す形にすることが理想です。
sazi

2020/09/18 05:40 編集

元データのパターンも欲しかったのですが。 推測して回答しておきます。 数量に関しては、元の質問にはないので考慮していません。
tailtera22

2020/09/18 07:03 編集

すみません、いただいたコードを反映させようとしましたが WHERE 文にIIf式を使用しているせいでエラーになってしまいます。 また、手数料のみにしてエラーを回避しても、スラックスの送料が両方消えてしまいました。 元データは以下のようになっています! 商品ID  商品名   商品価格  手数料  送料  値引き  数量 0001   シャツ    3000   330    0   0    1 0002    靴     5000    0   770   0    1 0003   ジャケット  10000   0    0   -1565   1 0003   ジャケット  10000   0    0   -1565   2 0004   スラックス  12000   0    0    0    1 ですので、以前いただいたSQLに手を加えて 以下のようにしています(こちらの諸事情で数値はすべて文字列型にしています)。 SELECT 商品ID, 商品名, 商品価格 AS 商品単価, 1 As Ord, 数量 FROM テーブル名 UNION ALL SELECT 商品ID, iif(手数料<>'0','手数料',iif(送料<>'0','送料',iif(値引き<>'0','値引き',''))), iif(手数料<>'0',手数料,iif(送料<>'0',送料,iif(値引き<>'0',値引き,'0'))), 2, 数量 FROM テーブル名 WHERE iif(手数料<>'0',手数料,iif(送料<>'0',送料,iif(値引き<>'0',値引き,'0'))) ORDER BY 商品ID, Ord; 結果は、以下のようになっています。 商品ID  商品名  商品単価  Ord  数量 0001   シャツ   3000   1   1 0001   手数料   330    2   1 0002   靴     5000   1   1 0002   送料    770    2   1 0003   ジャケット  10000  1   1 0003   ジャケット  10000  1   2 0003   値引き   -1565   2   1 0003   値引き   -1565   2   2 0004   スラックス  12000  1   1 ここから重複している送料を抜いて、以下のようにしたいです。 商品ID  商品名  商品単価  Ord  数量 0001   シャツ   3000   1   1 0001   手数料   330    2   1 0002   靴     5000   1   1 0002   送料    770    2   1 0003   ジャケット  10000  1   1 0003   ジャケット  10000  1   2 0003   値引き   -1565   2   1 0004   スラックス  12000  1   1
sazi

2020/09/18 07:17

コメントではなく、質問を編集して下さい。 このコメント欄は隠れているので、回答する側からは見落とす場合もあります。
guest

回答1

0

ベストアンサー

横を縦にする場合、その項目ごとにunion して下さい。
手数料、送料、値引き全てがある場合には、順番も必要でしょうし。

SQL

1 SELECT 商品ID, 商品名, 商品価格 AS 商品単価, 1 As Ord, 数量 2 FROM テーブル1 3UNION ALL 4 SELECT 商品ID, '手数料', min(手数料), 2, 1 5 FROM テーブル1 6 WHERE 手数料<>'0' 7 GROUP BY 商品ID 8UNION ALL 9 SELECT 商品ID, '送料', min(送料), 3, 1 10 FROM テーブル1 11 WHERE 送料<>'0' 12 GROUP BY 商品ID 13UNION ALL 14 SELECT 商品ID, '値引き', min(値引き), 4, 1 15 FROM テーブル1 16 WHERE 値引き<>'0' 17 GROUP BY 商品ID 18ORDER BY 商品ID, Ord, 数量

追記

追加するオートナンバーをIDとすると、相関副問合せで商品ID毎の最小のIDを求めます。

SQL

1 SELECT 商品ID, 商品名, 商品価格 AS 商品単価, 1 As Ord, 数量 2 FROM テーブル1 3UNION ALL 4 SELECT 商品ID, '手数料', 手数料, 2, 1 5 FROM テーブル1 t 6 WHERE 手数料<>'0' 7 and id=(select min(id) from テーブル1 where 商品ID=t.商品ID) 8UNION ALL 9 SELECT 商品ID, '送料', 送料, 3, 1 10 FROM テーブル1 t 11 WHERE 送料<>'0' 12 and id=(select min(id) from テーブル1 where 商品ID=t.商品ID) 13UNION ALL 14 SELECT 商品ID, '値引き', 値引き, 4, 1 15 FROM テーブル1 t 16 WHERE 値引き<>'0' 17 and id=(select min(id) from テーブル1 where 商品ID=t.商品ID) 18ORDER BY 商品ID, Ord, ID

投稿2020/09/18 05:38

編集2020/09/19 13:23
sazi

総合スコア25327

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tailtera22

2020/09/18 08:07

勝手が分からず、度々ご迷惑をおかけしましてすみません。 いただいたコードで目的の集計ができました! 順序まで設定できるようにしていただき、本当にありがとうございます。 UNION って1つしか設定できないかと思っていました! こんな使い方もあったのですね。大変勉強になります。 今後は質問の情報に気を付けて投稿させていただきますので、 これからも、どうかよろしくお願いいたします。
tailtera22

2020/09/18 10:02

sazi様 すみません、GROUP BYをつけると Replace関数や、フィールド1 & フィールド2 AS フィールドなどの割り当てが できなくなるのでしょうか? 以下のようなエラーが出てしまいます。 『集計関数の一部として指定された式'Replace(送料,'-','')'を含んでいないクエリを実行しようとしました。』
sazi

2020/09/18 11:10 編集

group byで指定していない固定値でないものは、何らかの集計関数(Max()やMin())を使用しないとエラーになります。 Replace(Min(送料),'-','')のようにして下さい。
sazi

2020/09/18 11:06

group byしたのは1行にする為なので、where条件で数量=1などで特定できるなら、group byは必要ありません。
tailtera22

2020/09/19 03:08 編集

sazi様 ご連絡が遅くなりまして、すみません。 Min()で囲っても解決できないフィールドが出てきたため、group byは難しいと考えました。 かといって数量の組み合わせは、2と3など1を含まない場合もありますから、1に固定することができませんし、一体どうすればよいのやら分からなくなりました。 送料、手数料、値引きを分けていただいたおかげで、 両方を含むレコードにも対応できましたし、以前よりはるかに分かり易く見やすくなりました。 where条件を設定してなんとか重複を無くしたいのですが、 元テーブルにどういったフィールドがあれば、group byを使わずに振り分けることが可能なのでしょうか?
sazi

2020/09/19 04:26 編集

> Min()で囲っても解決できないフィールドが出てきた どのようなものか想像できませんが。 手っ取り早いのはオートナンバーの項目です。 オートナンバーであれば行ごとに一意になりますから、商品IDごとの最小値とすれば特定は可能です。 尤も、正規化されずに、伝票と明細のようにテーブルが分かれていないのが根本的な原因だと思いますけれど。
sazi

2020/09/19 04:10 編集

質問の内容として前提が変わるのであれば、別質問にした方が良いですよ。
tailtera22

2020/09/19 10:38

>どのようなものか想像できませんが。 すみません >尤も、正規化されずに、伝票と明細のようにテーブルが分かれていないのが根本的な原因だと思いますけれど。 すみません >質問の内容として前提が変わるのであれば、別質問にした方が良いですよ。 すみません
tailtera22

2020/09/19 10:43

>手っ取り早いのはオートナンバーの項目です。 オートナンバーであれば行ごとに一意になりますから、商品IDごとの最小値とすれば特定は可能です。 商品IDごとの最小値といいますと WHERE ID min(商品ID) という感じでしょう?何度もこちらで質問してすみません
sazi

2020/09/19 13:30

追記しました。 商品ID毎の最小のIDの情報を使用することになるので、最小でないIDの行に値引きなどがあっても参照はされない事になります。
tailtera22

2020/09/19 16:55

ここまで長々とお付き合いしていただき、本当にありがとうございます。 tという文字が一体何を意味しているのかお伺いしてもよろしいでしょうか? これは変数みたいなものなのでしょうか? あれから、テーブルデータを見直して、2つのマスタテーブルを新たに作り、3つテーブルに分けてクエリを作成しました。 FROM (テーブル1 LEFT JOIN テーブル2 ON [テーブル1].商品CD = テーブル2.製品CD) LEFT JOIN テーブル3 ON ([テーブル1].氏名 = テーブル3.顧客名) AND ([テーブル1].[【お届け先】電話番号] = テーブル3.電話番号) こういった具合に繋げている場合、tの配置がどこに置けばよいのかと頭を抱えています。 FROM (テーブル1t LEFT でも「JOIN操作の構文エラー」で、一番後ろにつけても「クエリ式の構文エラー」となり、このtというものは単一のテーブルにのみ有効な記法なのでしょうか? これが解決できればようやく本件が解決できそうです。 どうかもう一度、お助け下さるとありがたいです!
sazi

2020/09/19 18:07

> tという文字が一体何を意味しているのかお伺いしてもよろしいでしょうか? テーブル名のエイリアス(別名)です。 「テーブル1 as t」※as は省略できます。 同じテーブル間での参照の場合、識別の為に別名を付けます。
tailtera22

2020/09/19 20:04

ご連絡ありがとうございます。 ASのことでしたか。ASはフィールドにしか設定できないと思っていました。 ただ、複数テーブルでのASがつけられずに困っております。 連結したテーブルの場合、どこに記述すればよいのでしょうか?
sazi

2020/09/20 01:59

分からなくなるなら、相関側にエイリアスして下さい and id=(select min(id) from テーブル1 as t where 商品ID=テーブル1.商品ID)
tailtera22

2020/09/20 05:31

ありがとうございます。 テーブルを複数繋げていない状態(テーブル1のみ)であれば、正しく集計されるのですが 前回お伝えした以下のFROMだと、やはり複数出てきてしまいます。 IDが両方表示されるので、 恐らくWHERE 文にあるIDのMIN()が、他のテーブルの参照のせいで効いていないのだと思います。 FROM (テーブル1 LEFT JOIN テーブル2 ON [テーブル1].商品CD = テーブル2.製品CD) LEFT JOIN テーブル3 ON ([テーブル1].氏名 = テーブル3.顧客名) AND ([テーブル1].[【お届け先】電話番号] = テーブル3.電話番号) この場合、複数表示させないようにするには、どうすればよいのでしょうか?
sazi

2020/09/20 05:51 編集

構文エラーは解決したのですよね。であれば相関部分で取得されているIDは一つです。 (複数返却されるとエラーになる記述なので) 取得した同じIDが複数表示されるのは、結合しているデータによるものです。 データやテーブル構造も示されない状態で、それに答えるのは無理です。 新たな質問にして下さい。
tailtera22

2020/09/20 09:50

わかりました。 またよろしくお願いします。
tailtera22

2020/09/20 10:08

原因が分かりました。 3つのテーブルそれぞれにIDフィールドが存在しているため 頂いたコードを以下のように編集していました。 and テーブル1.id=(select min( テーブル1.id) from テーブル1 as t where 商品ID=テーブル1.商品ID) 2つのマスタテーブルのIDフィールドを削除し、 コードを元に戻したところ、正しく集計されました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問