Query for Balance from two tables

[attapol]

Dear all,

I have two tables and I want to calculate the balance.

Table X ( record the product I sent to customer)
Customer------Product-----InQuantity
AAA------------A1-----------2
AAA------------A2-----------1
AAB------------A1-----------3
AAB------------A2-----------3

Table Z (recored the product which my customer can sell)
Customer-------Product--------OutQuantity
AAA--------------A1--------------1
AAB--------------A2--------------1

I want to query and want the result like this.

Balance
Customer------Product--------InQuantity--------OutQuantity-------Balance
AAA------------A1--------------2--------------------0-------------------2
AAA------------A2--------------1--------------------1-------------------0
AAB------------A1--------------3--------------------0-------------------3
AAB------------A2--------------3--------------------1-------------------2


I run the query with INNER JOIN , the result is correct but it miss some data. it shown only below.

AAA------------A2--------------1--------------------1-------------------0
AAB------------A2--------------3--------------------1-------------------2

I try to use the LEFT JOIN, but the result of Balance is not correct.

I do not know how to run query with the correct result

I will apprecitated for any advice.

Thank in advance
Attapol

 

[jdraw]

Please show us the underlying arithmetic that gives this result

Customer------Product--------InQuantity--------OutQuantity-------Balance
AAA------------A1--------------2--------------------0-------------------2

 

[plog]

First, you shouldn't store your debits and credits in seperate tables/fields. You should have them in the same table:

Transactions
trans_ID, autonumber, primary key
Customer, short text, holds what customer
Product, short text, holds what product
trans_Qty, number, number of product transacted (negative numbers denote debits)

With that structure getting the balance is a simple Totals Query (https://support.office.com/en-us/ar...uery-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a#bm1). With your set up you have to resort to trickery.

With that said, you would use a LEFT JOIN on your tables. The LEFT table (show all) would be Table X, and the other table (show just matches) would be Table Z. Additionally, you would have to make sure that Null results in Z get made into 0 using the NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php). So your balance calculation field would look like this:

Balance: SUM(inQuantity + NZ(outQuantity, 0))

 

[attapol]

Thank you jdraw for your reply.

I use this code.

SELECT TableX.Customer, TableX.Product, TableX.InQuantity, TableZ.OutQuantity, [TableX].[InQuantity]-[TableZ].[OutQuantity] AS Balance
FROM TableX INNER JOIN TableZ ON (TableX.Product = TableZ.Product) AND (TableX.Customer = TableZ.Customer) AND (TableX.Customer = TableZ.Customer)
GROUP BY TableX.Customer, TableX.Product, TableX.InQuantity, TableZ.OutQuantity, [TableX].[InQuantity]-[TableZ].[OutQuantity];

 

[arnelgp]

use Left Join.

SELECT TableX.Customer, TableX.Product, TableX.InQuantity, NZ(TableZ.OutQuantity,0) As OutQuantity, [TableX].[InQuantity]-Nz([TableZ].[OutQuantity], 0) AS Balance
FROM TableX LEFT JOIN TableZ ON (TableX.Product = TableZ.Product) AND (TableX.Customer = TableZ.Customer);

 

[attapol]

Thank you plog and arnelgp,

use Left Join.

SELECT TableX.Customer, TableX.Product, TableX.InQuantity, NZ(TableZ.OutQuantity,0) As OutQuantity, [TableX].[InQuantity]-Nz([TableZ].[OutQuantity], 0) AS Balance
FROM TableX LEFT JOIN TableZ ON (TableX.Product = TableZ.Product) AND (TableX.Customer = TableZ.Customer);

I try this and now it almost near my expect result but the calculation is not all correct.

As Nz function , it make some value in OutQuantity to zero. I do not know what is the cause? . It can change null in OutQuantity to zero but it also change some value to zero.

 

[attapol]

I have check again.

Now it work everything.

use Left Join.

SELECT TableX.Customer, TableX.Product, TableX.InQuantity, NZ(TableZ.OutQuantity,0) As OutQuantity, [TableX].[InQuantity]-Nz([TableZ].[OutQuantity], 0) AS Balance
FROM TableX LEFT JOIN TableZ ON (TableX.Product = TableZ.Product) AND (TableX.Customer = TableZ.Customer);

Many thanks arnelgp,