Query to get the balance

[SREE776]

Dear Friends;



Me very new to access. I am maintaining a DB for knowing the stock balance of toners. My DB contains the following the tables

01. Table Name - Printers - Field Name --> PrinterID, Description

02. Table Name - Toners - Field Name --> TonerID, Description, PrinterID

03. Table Name - PurchaseDetails - Field Name --> PONum, Date, TonerID, Qty

04. Table Name - IssueDetails - Field Name --> IssueNum, Date, TonerID, Qty

I created two queries

01. TotalPurchase - Fields - TonerID - (Grouped By), SumofQty - (Sum)

02. TotalIssue - Fields - TonerID - (Grouped By), SumofQty - (Sum)

Till here the things are fine but after this I dont know how to create the query so that I could get the stock balance of each toner.

I tried a query to get the balance but in the result I get the details of those items only which are issued. If any toner is not issued then I don't get the information about that in the StockBal query.

Please do help me to know how I could get the correct stock balance.

Thanks in advance for your support.

Regards

Sree

[pbaldy]

Try another query that uses the toners table as a base, and add the other two queries to it. Each of those should be joined to the table in such a way as all records from the toner table are returned. In design view you'd right-click on the join line to edit it. In SQL view, it would be a LEFT or RIGHT join instead of INNER.

[SREE776]

Try another query that uses the toners table as a base, and add the other two queries to it. Each of those should be joined to the table in such a way as all records from the toner table are returned. In design view you'd right-click on the join line to edit it. In SQL view, it would be a LEFT or RIGHT join instead of INNER.

Dear Pbaldy;

Thanks for the reply. As stated by you I did the same. I got the name of all the toners in the list but the balance I could get only of those items which I had issued. The items which I didn't issue it showing a null value as the balance though there is a stock balance in actual. The SQL statement for the query is as below

SELECT [Toner Details].ID, [TotalPurchase]![SumOfQty]-[IssueDetails]![SumOfQty] AS BALANCE
FROM ([Toner Details] LEFT JOIN IssueDetails ON [Toner Details].ID = IssueDetails.[Toner ID]) LEFT JOIN TotalPurchase ON [Toner Details].ID = TotalPurchase.[Toner ID];

I feel we are near and you would be able to help me to sort out the issue.

Regards

Sree

[pbaldy]

Try using the Nz() function around each value in the calculation.

[SREE776]

Thanks a lot Pbaldy. It really worked and helped me in solving the issue.

Thanks a lot.

Regards

Sree

[pbaldy]

Happy to help, and welcome to the site by the way.