I am newbie. I have 4 different tables that I want to join, here's the details:
1) Table: T_CODE
Item_Code
A
B
C
D
2) Table: T_MONTHLY_STOCK
Item_Code Qty Month
A 1000 201509
B 2000 201509
C 3000 201509
3) Table: T_SALES
Item_Code Qty Week
A 5 1
B 4 1
A 50 2
B 40 2
4) Table: T_RECEIPT
A 1 1
B 2 1
A 10 2
B 20 2
I would like to make a Query to get results as following:
Item_Code Stock In_Week_1 Out_Week_1 Balance_Week_1
A 1000 1 5 996
B 2000 2 4 1998
C 3000 0 0 3000
D 0 0 0 0
I have tried to make a query but I got the wrong result.
Item_Code Stock In_Week_1 Out_Week_1 Balance_Week_1
A 1000 210 992
B 2000 481996
C 3000 0 0 3000
D 0 0 0 0
my query as following:
SELECT T_CODE.Item_Code
, IIf(IsNull([T_MONTHLY_STOCK]![Item_code]),0,[T_MONTHLY_STOCK]![Qty]) AS Stock
, Sum(IIf([T_RECEIPT]![Week]=1,[T_RECEIPT]![Qty],0)) AS In_Week_1
, Sum(IIf([T_SALES]![Week]=1,[T_SALES]![Qty],0)) AS Out_Week_1
, IIf(IsNull([T_CODE]![Item_Code]),0,[T_MONTHLY_STOCK]![Qty])
+Sum(IIf([T_RECEIPT]![Week]=1,[T_RECEIPT]![Qty],0))
-Sum(IIf([T_SALES]![Week]=1,[T_SALES]![Qty],0)) AS Balance_Week_1
FROM
((T_CODE LEFT JOIN T_MONTHLY_STOCK ON T_CODE.Item_Code = T_MONTHLY_STOCK.Item_code)
LEFT JOIN T_SALES ON T_CODE.Item_Code = T_SALES.Item_code)
LEFT JOIN T_RECEIPT ON T_CODE.Item_Code = T_RECEIPT.Item_code
GROUP BY
T_CODE.Item_Code
, IIf(IsNull([T_CODE]![Item_Code]),0,[T_MONTHLY_STOCK]![Qty]);
Can anyone encountered this issue and knows what the fix is?
Thanks in advance!