Why do left join results not match in the MS Access database

Why do left join SQL query results not match in the MS Access database?

Maybe there is something wrong with the SQL code that I wrote?

If there is an answer then I don't want to use the function in MS Access because I want to use the SQL for VB.Net.

Table StocksinDetail

No	Invno	CodeProduct	Barcode	Colorcode	Size	Colorname	Qty
1	ITO-00004	TEST 1000	03-22164010	-	-	-	50
1	ITO-00001	TEST 1000	03-22164010	-	S	-	50
2	ITO-00001	TEST 1000	03-22164010	BHTF001	XL	Putih	50
3	ITO-00001	TEST 1000	03-22164010	BHTF002	M	-	50
4	ITO-00004	TEST 1001	03-22164010	BHTF001	-	Putih	50
1	ITO-00005	TEST 1000	03-22164010	-	-	-	50

Table StocksoutDetail

No	Invno	CodeProduct	Barcode	Colorcode	Size	Colorname	Qty
1	ITO-00001	TEST 1000	03-22164010	-	S	-	1
2	ITO-00001	TEST 1000	03-22164010	BHTF001	XL	Putih	1

Table Stocksfinal

CodeProduct	Barcode	Colorcode	Size	Colorname	Qty
TEST 1000	03-22164010	-	-	-	50
TEST 1000	03-22164010	-	-	-	50
TEST 1000	03-22164010	-	S	-	50
TEST 1000	03-22164010	BHTF001	XL	Putih	50
TEST 1000	03-22164010	BHTF002	M	-	50
TEST 1001	03-22164010	BHTF001	-	Putih	50

Desired result

CodeProduct	Barcode	Colorcode	Size	Colorname	Qty
TEST 1000	03-22164010	-	-	-	100
TEST 1000	03-22164010	-	S	-	50
TEST 1000	03-22164010	BHTF001	XL	Putih	50
TEST 1000	03-22164010	BHTF002	M	-	50
TEST 1001	03-22164010	BHTF001	-	Putih	50

SELECT  
    t1.CodeProduct, t1.Barcode, t1.Colorcode, t1.Size, 
    t1.ColorName,
    IIf(IsNull(t1.Qty),'0',t1.Qty)-IIf(IsNull(t2.Qty),'0',t2.Qty) AS QTY_STOCK,
    t1.Qty AS QTY_STOCKIN, t2.Qty AS QTY_STOCKOUT
FROM
    StocksinDetail AS t1
LEFT JOIN 
    StocksoutDetail AS t2 ON (t1.Size = t2.Size)
                          AND (t1.Colorcode = t2.Colorcode)
                          AND (t1.Barcode = t2.Barcode)
                          AND (t1.CodeProduct = t2.CodeProduct);

Solution

Your desired result doesn't show any data from StocksoutDetail. The output can be obtained with an aggregate query of StocksinDetail alone (Query1 below). If you want to pull data from both tables as shown in your SQL statement, build aggregate queries of each detail table then join those queries.

Consider:

Query1: SumIN

SELECT CodeProduct, Barcode, Colorcode, Colorname, Size, Sum(Qty) AS SumINQty
FROM StocksinDetail
GROUP BY CodeProduct, Barcode, Colorcode, Colorname, Size;

Query2: SumOUT

SELECT CodeProduct, Barcode, Colorcode, Size, Sum(Qty) AS SumOUTQty
FROM StocksoutDetail
GROUP BY CodeProduct, Barcode, Colorcode, Size;

Query3:

SELECT SumIN.*, SumOUTQty, SumINQty - Nz(SumOUTQty,0) AS StockQty
FROM SumIN LEFT JOIN SumOUT 
ON (SumIN.Size = SumOUT.Size) AND (SumIN.Colorcode = SumOUT.Colorcode) 
AND (SumIN.Barcode = SumOUT.Barcode) AND (SumIN.CodeProduct = SumOUT.CodeProduct);

CodeProduct	Barcode	Colorcode	Colorname	Size	SumINQty	SumOUTQty	StockQty
TEST 1000	03-22164010	-	-	-	100		100
TEST 1000	03-22164010	-	-	S	50	1	49
TEST 1000	03-22164010	BHTF001	Putih	XL	50	1	49
TEST 1000	03-22164010	BHTF002	-	M	50		50
TEST 1001	03-22164010	BHTF001	Putih	-	50		50

If you want to make it an all-in-one SQL, copy/paste SQL of each aggregate query (without semicolon) into 3rd query - resulting FROM clause would be like;

FROM (paste query1 here) AS SumIN LEFT JOIN (paste query2 here) AS SumOUT

Then can delete first 2 query objects. This is how to use Access query designer to create complex nested queries.

If details were in a single Transactions table with a field to designate if transaction were IN or OUT, one aggregate query would serve. So an alternative to the 3 queries is to build a UNION query that merges these 2 tables into a single dataset (as would be if they were one table to begin with) then use that query in another that aggregates.

SELECT CodeProduct, Barcode, Colorcode, Size, Colorname, 
   Sum(IIf(Cat="IN", Qty, 0)) AS StocksIN, 
   Sum(IIf(Cat="Out", Qty, 0)) AS StocksOUT, 
   Sum(Qty * IIf(Cat="OUT", -1, 1)) AS StocksFinal 
FROM
   (SELECT [No], Invno, CodeProduct, Barcode, Colorcode, Size, Colorname, Qty, "IN" AS Cat FROM StocksinDetail
    UNION SELECT [No], Invno, CodeProduct, Barcode, Colorcode, Size, Colorname, Qty, "OUT" FROM StocksoutDetail) AS DetailUNION
GROUP BY CodeProduct, Barcode, Colorcode, Size, Colorname;