simple " quanity on hand" query

[tcommis]

I'll try and put this a simple as possible - I know it can't be that hard.

I have 2 tables - chemicals purchased and chemicals used and two querys that give me the sum of each

the 3rd query is the (sum of chemical x purchased) - (sum of chemical x used) which gives me the sum of chemical x "on hand"

problem is if I don't have any usage for that chemical the query won't return a value.

I know Nz must play a part but all attemps have not worked.

 

[dcx693]

You need to have your tables joined the proper way, and Nz() should not be necessary.

You want to view all the records from the chemicals purchased table, whether or not they have corresponding records in the chemicals used table. That calls for a LEFT (or RIGHT) join. That means in your query, when you join the tables, you need to change the type of join from showing only those records where the joined field is equal to showing all records from the chemicals purchased table whether or not they have a matching record on the chemicals used table. The join line should then show an arrow from the chemicals purchased table pointing to the chemicals used table.

 

[liddlem]

Hi tcommis
Dont know if this will work, but give it a go.
Try run an update query for all records where the used value is null? (Changing the value to zero)

Then run your original query.

 

[tcommis]

Thanks Dcx693 that worked - knew it had to be easy.

 

[Pat Hartman]

Changing the stored value from null to 0 by running an update query would not solve the problem and would also be incorrect at a logical level.

Use a left join and use the Nz() function to surround the used value.

Sum(Purchased) - Sum(Nz(Used))