【最大10000ポイント】当たる!!質問投稿キャンペーン!

テーブル1
販売店名 品名  金額
A販売店/いちご/500円
A販売店/みかん/300円
A販売店/りんご/800円
B販売店/いちご/500円
B販売店/みかん/300円
B販売店/りんご/800円
B販売店/ばなな/400円
C販売店/いちご/500円
C販売店/みかん/300円
C販売店/りんご/800円

テーブル2
販売店名 品名1 品名2 品名3 品名4
A販売店/いちご/みかん/りんご
B販売店/いちご/みかん/りんご/ばなな
C販売店/いちご/みかん/りんご

これを下記のように結合したい
販売店名 品名1 金額   品名2 金額   品名3 金額   品名4 金額
A販売店/いちご/500円/みかん/300円/りんご/800円
B販売店/いちご/500円/みかん/300円/りんご/800円/ばなな/400円
C販売店/いちご/500円/みかん/300円/りんご/800円


どのようにすればよいでしょうか?
クエリーだけでも可能ですかね?

A 回答 (1件)

2つの方法で



金額部分は数値であるとします
なお、希望の最終形表示での、"金額" ・・・
同じ文字列のフィールドは作成できないので 金額1、金額2 ・・・


1)DLookup を使う

SELECT 販売店名,
品名1,
DLookup("金額","テーブル1","販売店名='" & 販売店名
& "' AND 品名='" & 品名1 & "'") AS 金額1,
品名2,
DLookup("金額","テーブル1","販売店名='" & 販売店名
& "' AND 品名='" & 品名2 & "'") AS 金額2,
品名3,
DLookup("金額","テーブル1","販売店名='" & 販売店名
& "' AND 品名='" & 品名3 & "'") AS 金額3,
品名4,
DLookup("金額","テーブル1","販売店名='" & 販売店名
& "' AND 品名='" & 品名4 & "'") AS 金額4
FROM テーブル2;


2)クロス集計を使う

以下内容で、クエリ ★★ を作っておいて

SELECT 1 AS 順, 販売店名, 品名1 AS 品名 FROM テーブル2
WHERE 品名1 Is Not Null
UNION ALL
SELECT 2, 販売店名, 品名2 FROM テーブル2
WHERE 品名2 Is Not Null
UNION ALL
SELECT 3, 販売店名, 品名3 FROM テーブル2
WHERE 品名3 Is Not Null
UNION ALL
SELECT 4, 販売店名, 品名4 FROM テーブル2
WHERE 品名4 Is Not Null;


以下のクロス集計で最終形にする


TRANSFORM First(IIF(S=1,Q2.品名,Q2.金額)) AS 値
SELECT Q1.販売店名 FROM (
SELECT 1 AS S, * FROM ★★
UNION ALL
SELECT 2, * FROM ★★) AS Q1
INNER JOIN テーブル1 AS Q2
ON Q1.販売店名=Q2.販売店名 AND Q1.品名=Q2.品名
GROUP BY Q1.販売店名
PIVOT IIF(S=1,'品名','金額') & Q1.順
IN ('品名1','金額1','品名2','金額2','品名3','金額3','品名4','金額4');


余談)テーブル1だけからでも以下の様な表示は可能

どのように表示されるのかは、実際にやってみてください

TRANSFORM Sum(金額) AS 値
SELECT 販売店名, Sum(金額) AS 計 FROM (
SELECT 1 AS T, 販売店名, 品名, 金額 FROM テーブル1
UNION ALL
SELECT 2, '合計', 品名, 金額 FROM テーブル1) AS Q1
GROUP BY T, 販売店名
PIVOT 品名;
    • good
    • 0
この回答へのお礼

30246kiku様ありがとうございました。
クエリ一つでこんなに出来あがるなんて、嬉しい限りです。

お礼日時:2016/05/04 17:23

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QAccessで別テーブルの値をフォームに表示したい

初めてのAccessで分からない事があり質問させてください。

<会社テーブル>
会社ID
会社名
住所

<社員テーブル>
会社ID
社員名
ソート番号

*1社に対し複数の社員レコードが存在

以上のようなテーブルがあるとします

現在「会社テーブル」を表形式で一覧表示しています
会社ID,会社名、住所とフィールドが並んでいるのですが、その後ろに「社員テーブル」のソート番号が一番若い社員名を表示したいと考えています(現状は番号関係なく表示させる方法すら分かりません)

色々いじくりまわしたのですが、初めてAccessをさわる事もあってよくわかりません

リレーション等でひっぱってくる事ができるのでしょうか?

どなたか教えて頂けませんでしょうか

何卒よろしくお願い致します

PS.Access2013で作成中です

Aベストアンサー

単純にテーブル[会社]のみでフォームを作成し、社員表示用テキストボックスを追加。そして、そのプロパティを表示し

コントロールソース=DBLookup("SELECT 社員名 FROM 社員 WHERE 会社ID=" & [会社ID] & " ORDER BY ソート番号")

これは、以下の標準モジュールに登録したユーザ関数 DBLookup()を使っています。これで、クエリを用意しなくても目的を達成できます。

さて、かかるユーザ関数を標準ライブラリに登録したくない場合、

1、"社員ソートクエリ"を作成します。

SELECT 社員.会社ID, 社員.社員名
FROM 社員
ORDER BY 社員.ソート番号;

2、コントロールソースに次のように書きます。

=DLookUp("社員名","社員ソートクエリ","会社ID=" & [会社ID])

http://office.microsoft.com/ja-jp/access-help/HA001228825.aspx

DLookup()については、マイクロソフトの解説を参照されてください。

【DLookup()の限界を破るにはVBAで同じ関数を作るしかない】

マイクロソフトの解説を読めば判りますが、ORDER BY 節を指定する引数が用意されていません。ですから、どうしても、"社員ソートクエリ"を作成するという手間が必要となります。そこで、SQL文を引数とするDBLookup()をVBAで書けば、その手間を省けるという算段になります。この辺りは、好みと趣味の問題。どっちでも良いと思います。そういうお断りをした上で DBLookup()を紹介しておきます。なお、ADOは、つぎのように参照設定しないと利用できません。

http://www.happy2-island.com/access/gogo03/capter00307.shtml

Public Function DBLookup(ByVal strQuerySQL As String, _
             Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
  Dim DataValue
  Dim rst     As ADODB.Recordset

  Set rst = New ADODB.Recordset
  With rst
    .Open strQuerySQL, _
       CurrentProject.Connection, _
       adOpenStatic, _
       adLockReadOnly
    If Not .BOF Then
      .MoveFirst
      DataValue = .Fields(0)
    End If
  End With
Exit_DBLookup:
On Error Resume Next
  rst.Close
  Set rst = Nothing
  DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
  Exit Function
Err_DBLookup:
  MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
      "・Err.Description=" & Err.Description & Chr$(13) & _
      "・SQL Text=" & strQuerySQL, _
      vbExclamation, " 関数エラーメッセージ"
  Resume Exit_DBLookup
End Function

単純にテーブル[会社]のみでフォームを作成し、社員表示用テキストボックスを追加。そして、そのプロパティを表示し

コントロールソース=DBLookup("SELECT 社員名 FROM 社員 WHERE 会社ID=" & [会社ID] & " ORDER BY ソート番号")

これは、以下の標準モジュールに登録したユーザ関数 DBLookup()を使っています。これで、クエリを用意しなくても目的を達成できます。

さて、かかるユーザ関数を標準ライブラリに登録したくない場合、

1、"社員ソートクエリ"を作成します。

SELECT 社員.会社ID, 社員.社員名
FR...続きを読む


人気Q&Aランキング