creating a query

[Alex]

I have three tables:

Table1 - All Product
Day Product Component# Qty
1/1/04 A 7 100
1/2/04 C 2 20
1/3/04 C 4 10
1/4/04 D 4 40
1/5/04 A 2 30

Table2 - Used Product
Day Product Component# Qty
1/3/04 A 7 20
1/4/04 C 4 3
1/4/04 A 7 40
1/5/04 A 2 10

Table3 - Thrown Out Product
Day Product Component# Qty
1/3/04 A 7 10
1/5/04 D 4 40

How could I create a query to get as a result all data
from Table1 (Day can be the same) but with Qty reduced by
Qty from Table2 and 3 for matched Product and Component#?

QtyTable1 - QtyTable2 - QtyTable3

Thanks

 

[[MVP] S.Clark]

Add all 3 tables to the query.

Link 1 and 2 with a Left Outer Join
Link 1 and 3 with a Left Outer Join

Create the expression:

MyCalc: Table1.Qty - nz(Table2.Qty,0) - nz(Table3.Qty,0)

 

[Alex]

Thanks a lot.
Could you please advise what a feild should be used for
the link?
The Qty should be calculated (deducted) for the same
Product and Component#.

Thanks

 

[John Vinson]

Thanks a lot.
Could you please advise what a feild should be used for
the link?
The Qty should be calculated (deducted) for the same
Product and Component#.

Then use those two fields. A link can consist of one to ten fields.