How to create a SQL item history stockcard in the MS ACCESS database in VB.NET

I'm trying to create a SQL item history stockcard using an MS Access database in VB.NET. I have the SQL below, but it's not right yet:

SELECT CodeProduct, Sum(IIf(Cat="IN", Qty, 0)) AS [IN], Sum(IIf(Cat="Out", Qty, 0)) AS OUT, Sum(Qty * IIf(Cat="OUT", -1, 1)) AS BALANCE
FROM (
    SELECT [No], Invono, CodeProduct,Qty, "IN" AS Cat FROM PurchaseDetail
    UNION 
    SELECT [No], Invono, CodeProduct, Qty, "OUT" FROM SalesDetail
)  AS DetailUNION
WHERE CodeProduct = 'A'
GROUP BY CodeProduct;

Results from the above SQL code

CodeProduct	IN	OUT	BALANCE
A	75	65	10

Sample Data :

Table Purchase

Invono	Invodate	Transaction	Remark	NameSC
1000	18-Oct-23	Purchase	REPEAT AGAIN	TEST1
1001	19-Oct-23	Purchase		TEST2

Table PurchaseDetail

Invono	No	Codeproduct	Qty	Info
1000	1	A	50	WHITE
1000	2	B	35
1001	1	C	17
1001	2	A	25	BROWN

Table Sales

Invono	Invodate	Transaction	NameSC
1000	18-Oct-23	Sales	TEST10
1001	19-Oct-23	Sales	TEST20
1002	20-Oct-23	Sales	TEST30

Table SalesDetail

Invono	No	Codeproduct	Qty	Info
1000	1	A	25	WHITE
1001	1	C	35
1001	2	A	15	WHITE
1002	1	A	25	BROWN

Desired Result

Invono	Invodate	Transaction	No	Codeproduct	Info	Remark	NameSC	IN	OUT	BALANCE
1000	18-Oct-23	Purchase	1	A	WHITE	REPEAT AGAIN	TEST1	50		50
1000	18-Oct-23	Sales	1	A	WHITE		TEST10		25	25
1001	19-Oct-23	Purchase	2	A	BROWN		TEST2	25		50
1001	19-Oct-23	Sales	2	A	WHITE		TEST20		15	35
1002	20-Oct-23	Sales	1	A	BROWN		TEST30		25	10
...

Solution

Include Sales and Purchase tables in the UNION lines with INNER JOIN:

SELECT InvoDate, Transaction, InvoNo, CodeProduct, NameSC, 
Sum(IIf(Transaction="Purchase", Qty, 0)) AS [IN], 
Sum(IIf(Transaction="Sales", Qty, 0)) AS OUT, 
Sum(Qty * IIf(Transaction="Sales", -1, 1)) AS BALANCE
FROM (
    SELECT [No], InvoDate, Purchase.Invono, CodeProduct, Qty, NameSC, Transaction 
    FROM PurchaseDetail INNER JOIN Purchase 
    ON Purchase.Invono = PurchaseDetail.Invono
    UNION 
    SELECT [No], InvoDate, Sales.Invono, CodeProduct, Qty, NameSC, Transaction 
    FROM SalesDetail INNER JOIN Sales 
    ON Sales.Invono = SalesDetail.Invono
)  AS DetailUNION
WHERE CodeProduct = 'A'
GROUP BY InvoDate, Transaction, InvoNo, CodeProduct, NameSC;

InvoDate	Transaction	InvoNo	CodeProduct	NameSC	IN	OUT	BALANCE
10/18/2023	Purchase	1000	A	TEST1	50	0	50
10/18/2023	Sales	1000	A	TEST10	0	25	-25
10/19/2023	Purchase	1001	A	TEST2	25	0	25
10/19/2023	Sales	1001	A	TEST20	0	15	-15
10/20/2023	Sales	1002	A	TEST30	0	25	-25

The BALANCE shown in your output sample is a running total. Running total in query is a common topic. It is difficult to accomplish in Access query. One approach uses DSum(), another requires correlated subquery. Each can perform slowly with large dataset. It is simple to produce a running total in Access report using Sorting & Grouping and textbox RunningSum property.

Your running balance is by CodeProduct. Following version shows running total. Unfortunately, value on each row will not agree with IN OUT BALANCE values because running total subquery is not calculating records in the same order. Also, when there are multiple records for the same date, they show the same value because there is no way to differentiate them in the subquery for sequential calc. This is usually accomplished by querying a table directly and including a unique ID field such as autonumber field in the WHERE clause. Since your data set is a complex query, not just a table, this is not available. However, final balance on last row is correct. Excluded WHERE clause so results will show for all CodeProduct values. Add filtering as you see fit.

First, the DetailUNION SQL must be a saved query object that is then called to perform the aggregation and running total. Embedding its SQL as a subquery fails.

New Info: Including NameSC criteria in RunningBal subquery does allow differentiation of multiple same-date records and calculations will then agree. However, I doubt actual data in table can be relied on.

SELECT InvoDate, Transaction, InvoNo, CodeProduct, NameSC, 
Sum(IIf(Transaction="Purchase",Qty,0)) AS [IN], 
Sum(IIf(Transaction="Sales",Qty,0)) AS OUT, 
Sum(Qty*IIf(Transaction="Sales",-1,1)) AS BALANCE,
(SELECT Sum(Qty * IIf(Transaction="Sales",-1,1)) 
 FROM DetailUNION as Dupe 
 WHERE Dupe.CodeProduct=DetailUNION.CodeProduct 
 AND Dupe.InvoDate<=DetailUNION.InvoDate 
 AND Dupe.NameSC<=DetailUNION.NameSC) AS RunningBal
FROM DetailUNION
GROUP BY InvoDate, Transaction, InvoNo, CodeProduct, NameSC
ORDER BY CodeProduct, InvoDate, NameSC;

InvoDate	Transaction	InvoNo	CodeProduct	NameSC	IN	OUT	BALANCE	RunningBal
10/18/2023	Purchase	1000	A	TEST1	50	0	50	50
10/18/2023	Sales	1000	A	TEST10	0	25	-25	25
10/19/2023	Purchase	1001	A	TEST2	25	0	25	50
10/19/2023	Sales	1001	A	TEST20	0	15	-15	35
10/20/2023	Sales	1002	A	TEST30	0	25	-25	10
10/18/2023	Purchase	1000	B	TEST1	35	0	35	35
10/19/2023	Purchase	1001	C	TEST2	17	0	17	17
10/19/2023	Sales	1001	C	TEST20	0	35	-35	-18