Microsoft Access 掲示板

以下続き

q在庫表仕入
SELECT
 t商品マスタ.商品ID,
 t商品マスタ.商品名,
 t商品マスタ.期首数量,
 t商品マスタ.期首金額,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q仕入明細]![仕入数量])) AS 7月仕入数量,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q仕入明細]![仕入金額])) AS 7月仕入金額,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q仕入明細]![仕入数量])) AS 8月仕入数量,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q仕入明細]![仕入金額])) AS 8月仕入金額,
・・・・・以下繰り返し

FROM t決算日, t商品マスタ INNER JOIN q仕入明細 ON t商品マスタ.商品ID = q仕入明細.仕入商品ID
GROUP BY t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額;

q在庫表売上
SELECT
 t商品マスタ.商品ID,
 t商品マスタ.商品名,
 t商品マスタ.期首数量,
 t商品マスタ.期首金額,
 Sum(IIf([q売上明細]![売上日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q売上明細]![売上数量])) AS 7月売上数量,
 Sum(IIf([q売上明細]![売上日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q売上明細]![売上数量])) AS 8月売上数量,
 ・・・・・以下繰り返し
FROM t決算日, q売上明細 INNER JOIN t商品マスタ ON q売上明細.売上商品ID = t商品マスタ.商品ID
GROUP BY t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額;