Inventory Query Calculations

[tscotti]

Within my "Products" form, I have a "Transactions" subform whose records are
categorized in five different type of inventory transactions - let's say "A", "B", "C", "D", and "E".

I'm using linked queries (instead of DSum) to calculate the net inventory amount (A+B+C-D-E) per product - sort of a running total.

The problem is that transaction type "E" may not appear for a particular record!

Consequently, the query calculations omit those products without transaction
type "E".

How can I include all the "Products" records into my inventory query calculations even though a product may not contain all the transaction types in their records?

Thanks, in advance, for responding.

Tony

 

[boblarson]

Use a left OUTER join (in the QBE window you can right click on the link between the tables and set it to Select ALL from Products and only those records where XX match).

 

[tscotti]

Bob,

Thanks for your reply.

I've tried your suggestion but I'm getting mixed results.

My form and query design is probably flawed.

Is there any chance that you can have a look at them?

Thanks,

Tony

 

[boblarson]

If you can either post a stripped down version here (after zipping it has to be 393Kb or smaller) or email to me (PM me for my email address) and I will try to take a look.

 

[tscotti]

Hi Bob,

Please find attached my inventory DB.

I may have fixed the issue using the LEFT join as you suggested and "Nz".

However, I'm not sure about my approach and whether it's efficient or not.

Please advise.

Thanks very much, in advanced, for your help.

Regards,

Tony