ACCESS 2013 クロス集計でのGROUP BYの使い方について

Question

###前提・実現したいこと
Access2013を使って在庫管理システムを作っています。

毎週一回、アイテムの棚卸をしてItemごと、ビル毎の在庫数量を入力しています。

Table 名 T_In/out

ID As of Building Item Qty
35 10/5/2015 A Display (22inch) 77 ← これ
34 10/5/2015 A Display (24inch) 90 ← これ
15 10/5/2015 B Display (24inch) 8 ← これ
14 10/5/2015 B Display (22inch) 60 ← これ
33 10/3/2015 B Display (24inch) 5
32 10/3/2015 B Display (22inch) 50
31 10/3/2015 A Display (24inch) 500
30 10/3/2015 A Display (22inch) 500
22 9/25/2015 B IP Phone 6 ← これ
21 9/25/2015 A IP Phone 90 ← これ

最新の在庫数量をPivotで表示させたく、下記のSQLで実施すると、BuildAは問題ないのですが、BuildingBの数量が最新の日付のものが反映されず困っています。

下記のソースコードを実行した結果

Item Date A B
Display (22inch) 05-Oct-15 77 50（本当は60）
Display (24inch) 05-Oct-15 90 5 （本当は８）
IP Phone (335) 25-Sep-15 90 6

###ソースコード
SQL (Access 2013)

TRANSFORM Last([T_In/out].Qty) AS Date
SELECT [T_In/out].Item, Max([T_In/out].[As of]) AS [Date]
FROM [T_In/out]
GROUP BY [T_In/out].Item
PIVOT [T_In/out].Building;

Access初心者でいろいろと調べてみたのですが解決できずにいます。
どなたかアドバイス頂けると幸いです。

どうぞよろしくお願い致します。

Answer 1

First関数、Last関数の仕様でそうなります。詳細は下記のリンク先を参照してください。
First および Last 関数の仕様
要するにこのような要件では、First関数、Last関数はどのレコードを返すか不定なので使えないということです。

方法としては、Item と ビル でグループ化して日付の最大を取得する集計クエリを作成して、それと元のテーブルを結合したものをクロス集計クエリにします。

Q_MaxDate

SELECT [T_In/out].Item, [T_In/out].Building, Max([T_In/out].[As of]) AS [Date]
FROM [T_In/out]
GROUP BY [T_In/out].Item, [T_In/out].Building;

QX_In/out

TRANSFORM First([T_In/out].Qty) AS FQty
SELECT Q_MaxDate.Item, Q_MaxDate.Date
FROM [T_In/out] INNER JOIN Q_MaxDate ON ([T_In/out].Item = Q_MaxDate.Item) AND ([T_In/out].[As of] = Q_MaxDate.[Date]) AND ([T_In/out].Building = Q_MaxDate.Building)
GROUP BY Q_MaxDate.Item, Q_MaxDate.Date
PIVOT Q_MaxDate.Building;

２つのクエリになりますが、サブクエリを使えば一つのクエリにすることも可能です。

相関サブクエリを使う方法もありますが重くなりがちです。上記の集計クエリと結合する方法が最速だと思います。

Comments

[anemone1005]

早速のコメントありがとうございます！
頂いたアドバイスで臨んだ結果が得られました。ありがとうございます！

いろいろと試していた時に、日付列でMAXをかけていたはず（つもり）なのに、よく見てみたらIDのMAXをとっているようで、はまっていました。

First/Last関数についてもよく調べてみます！
またINNER JOINは連結関数？のようなものでしょうか。こちらも初めてなので調べてみます。
ありがとうございました！