Calculation in Query

[Guest]

I have a shipped table and I have a received table - the two tables are
joined by Product ID - in my query I am trying to calculate actual inventory
totals (received - shipped).

I am not getting the answer to come up. How do I group by product ID and
get all of the received counts to add up and add up all of the shipped counts
and subtract the 2?

 

[Nikos Yannacopoulos]

Erika,

In a good design, you should have a Products table with all the products
in it. Furthermore, you would have a totals query summing quantities per
ProdictID in each of the other two tables. So, to get your inventory,
you would use the Products table as your main table in another query,
and join the two totals queries on it (outer join) so next to each
ProductID you get a number for total shipments and one for total
receipts (or nothing if no shipments or receipts for a ProductID). A
calculated field on the two will give you the subtraction (Use function
Nz to return 0's if no number returned).

HTH,
Nikos

 

[Guest]

When you say Use function Nz to return 0's if no number returned, I am not
sure what that is? Could you help me with that?

 

[Van T. Dinh]

It is an Access function that replace the Null value with whatever you
choose. For example:

Nz([YourField], 0)

will return zero if [YouField] has Null value.

Check Access VB Help (which should be the first source of info. when you use
Access).