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

Question

すみません、以下のページで問題は解決していたのですが
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のような特殊なパターンがあり
スラックスの重複は残しつつ
一番下の送料レコードの重複分だけを除外したいのですが、どうやってもうまくいきません。

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

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

Comments

[sazi]

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

[tailtera22]

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]

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

[tailtera22]

すみません、いただいたコードを反映させようとしましたが
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]

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

Answer 1

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

  SELECT 商品ID, 商品名, 商品価格 AS 商品単価, 1 As Ord, 数量
  FROM テーブル1
UNION ALL 
  SELECT 商品ID, '手数料', min(手数料), 2, 1
  FROM テーブル1
  WHERE 手数料<>'0'
  GROUP BY 商品ID
UNION ALL 
  SELECT 商品ID, '送料', min(送料), 3, 1
  FROM テーブル1
  WHERE 送料<>'0'
  GROUP BY 商品ID
UNION ALL 
  SELECT 商品ID, '値引き', min(値引き), 4, 1
  FROM テーブル1
  WHERE 値引き<>'0'
  GROUP BY 商品ID
ORDER BY 商品ID, Ord,  数量

追記

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

  SELECT 商品ID, 商品名, 商品価格 AS 商品単価, 1 As Ord, 数量
  FROM テーブル1
UNION ALL 
  SELECT 商品ID, '手数料', 手数料, 2, 1
  FROM テーブル1 t
  WHERE 手数料<>'0'
    and id=(select min(id) from テーブル1 where 商品ID=t.商品ID)
UNION ALL 
  SELECT 商品ID, '送料', 送料, 3, 1
  FROM テーブル1 t
  WHERE 送料<>'0'
    and id=(select min(id) from テーブル1 where 商品ID=t.商品ID)
UNION ALL 
  SELECT 商品ID, '値引き', 値引き, 4, 1
  FROM テーブル1 t
  WHERE 値引き<>'0'
    and id=(select min(id) from テーブル1 where 商品ID=t.商品ID)
ORDER BY 商品ID, Ord, ID

Comments

[tailtera22]

勝手が分からず、度々ご迷惑をおかけしましてすみません。

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

[tailtera22]

sazi様

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

[sazi]

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

[sazi]

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

[tailtera22]

sazi様

ご連絡が遅くなりまして、すみません。
Min()で囲っても解決できないフィールドが出てきたため、group byは難しいと考えました。
かといって数量の組み合わせは、２と３など１を含まない場合もありますから、１に固定することができませんし、一体どうすればよいのやら分からなくなりました。

送料、手数料、値引きを分けていただいたおかげで、
両方を含むレコードにも対応できましたし、以前よりはるかに分かり易く見やすくなりました。
where条件を設定してなんとか重複を無くしたいのですが、
元テーブルにどういったフィールドがあれば、group byを使わずに振り分けることが可能なのでしょうか？

[sazi]

> Min()で囲っても解決できないフィールドが出てきた
どのようなものか想像できませんが。

手っ取り早いのはオートナンバーの項目です。
オートナンバーであれば行ごとに一意になりますから、商品IDごとの最小値とすれば特定は可能です。

尤も、正規化されずに、伝票と明細のようにテーブルが分かれていないのが根本的な原因だと思いますけれど。

[sazi]

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

[tailtera22]

＞どのようなものか想像できませんが。
すみません

>尤も、正規化されずに、伝票と明細のようにテーブルが分かれていないのが根本的な原因だと思いますけれど。
すみません

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

[tailtera22]

＞手っ取り早いのはオートナンバーの項目です。
オートナンバーであれば行ごとに一意になりますから、商品IDごとの最小値とすれば特定は可能です。

商品IDごとの最小値といいますと
WHERE ID min(商品ID)
という感じでしょう？何度もこちらで質問してすみません

[sazi]

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

[tailtera22]

ここまで長々とお付き合いしていただき、本当にありがとうございます。

tという文字が一体何を意味しているのかお伺いしてもよろしいでしょうか？
これは変数みたいなものなのでしょうか？

あれから、テーブルデータを見直して、２つのマスタテーブルを新たに作り、３つテーブルに分けてクエリを作成しました。

FROM (テーブル１ LEFT JOIN テーブル２ ON [テーブル１].商品CD = テーブル２.製品CD) LEFT JOIN テーブル３ ON ([テーブル１].氏名 = テーブル３.顧客名) AND ([テーブル１].[【お届け先】電話番号] = テーブル３.電話番号)

こういった具合に繋げている場合、tの配置がどこに置けばよいのかと頭を抱えています。
FROM (テーブル１t LEFT でも「JOIN操作の構文エラー」で、一番後ろにつけても「クエリ式の構文エラー」となり、このｔというものは単一のテーブルにのみ有効な記法なのでしょうか？

これが解決できればようやく本件が解決できそうです。
どうかもう一度、お助け下さるとありがたいです！

[sazi]

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

[tailtera22]

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

[sazi]

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

[tailtera22]

ありがとうございます。

テーブルを複数繋げていない状態（テーブル１のみ）であれば、正しく集計されるのですが
前回お伝えした以下のFROMだと、やはり複数出てきてしまいます。
IDが両方表示されるので、
恐らくWHERE 文にあるIDのMIN()が、他のテーブルの参照のせいで効いていないのだと思います。

FROM (テーブル１ LEFT JOIN テーブル２ ON [テーブル１].商品CD = テーブル２.製品CD) LEFT JOIN テーブル３ ON ([テーブル１].氏名 = テーブル３.顧客名) AND ([テーブル１].[【お届け先】電話番号] = テーブル３.電話番号)

この場合、複数表示させないようにするには、どうすればよいのでしょうか？

[sazi]

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

[tailtera22]

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

[tailtera22]

原因が分かりました。

３つのテーブルそれぞれにIDフィールドが存在しているため
頂いたコードを以下のように編集していました。

and テーブル１.id=(select min( テーブル１.id) from テーブル1 as t where 商品ID=テーブル１.商品ID)

２つのマスタテーブルのIDフィールドを削除し、
コードを元に戻したところ、正しく集計されました。