creating a query

[Alex]

Thanks a lot.
But, using this I'm getting extra rows.

SELECT Table1.Day, Table1.[Product Code],
Table1.Component, Table1.Qty, Table2.Qty, Table3Qty
FROM (Table1 LEFT JOIN Table2 ON (Table1.Component =
Table2.Component) AND (Table1.[Product Code] = Table2.
[Product Code])) LEFT JOIN Table3 ON (Table1.Component =
Table3.Component) AND (Table1.[Product Code] = Table3.
[Product Code])
WHERE (Table1.Day)>=#5/1/2004# And (Table1.Day)
<=#5/31/2004#;Subject: Re: creating a query

...........................................................

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.



....................................................
From: "[MVP] S.Clark" <[email protected]>
Sent: 6/4/2004 2:50:21 PM

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)


--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/


message

 

[Alex]

Now, I'm using the following but still having some extra
rows:

SELECT Table1.Day, Table1.[Product Code],
Table1.Component, Table1.Qty, Table2.Qty, Table3Qty
FROM (Table1 LEFT JOIN Table2 ON (Table1.Day = Table2.Day)
AND(Table1 LEFT JOIN Table2 ON (Table1.Component =
Table2.Component) AND (Table1.[Product Code] = Table2.
[Product Code])) LEFT JOIN Table3 ON (Table1.Component =
Table3.Component) AND (Table1.[Product Code] = Table3.
[Product Code])

Thanks

-----Original Message-----
Thanks a lot.
But, using this I'm getting extra rows.

SELECT Table1.Day, Table1.[Product Code],
Table1.Component, Table1.Qty, Table2.Qty, Table3Qty
FROM (Table1 LEFT JOIN Table2 ON (Table1.Component =
Table2.Component) AND (Table1.[Product Code] = Table2.
[Product Code])) LEFT JOIN Table3 ON (Table1.Component =
Table3.Component) AND (Table1.[Product Code] = Table3.
[Product Code])
WHERE (Table1.Day)>=#5/1/2004# And (Table1.Day)
<=#5/31/2004#;Subject: Re: creating a query

.......................................................... ..

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.



....................................................
From: "[MVP] S.Clark" <[email protected]>
Sent: 6/4/2004 2:50:21 PM

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)


--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/


message

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

.

 

[John Vinson]

Now, I'm using the following but still having some extra
rows:

SELECT Table1.Day, Table1.[Product Code],
Table1.Component, Table1.Qty, Table2.Qty, Table3Qty
FROM (Table1 LEFT JOIN Table2 ON (Table1.Day = Table2.Day)
AND(Table1 LEFT JOIN Table2 ON (Table1.Component =
Table2.Component) AND (Table1.[Product Code] = Table2.
[Product Code])) LEFT JOIN Table3 ON (Table1.Component =
Table3.Component) AND (Table1.[Product Code] = Table3.
[Product Code])

Does a given record in Table1 match up to two or more records in
Table2, and to two or more records in Table3? If so, you'll get all
possible combinations of records in Table2 and Table3.

 

[Alex]

Thanks, John.
Yes, it's true.
May be I need to use Recordset to handle with it?

-----Original Message-----

Now, I'm using the following but still having some extra
rows:

SELECT Table1.Day, Table1.[Product Code],
Table1.Component, Table1.Qty, Table2.Qty, Table3Qty
FROM (Table1 LEFT JOIN Table2 ON (Table1.Day = Table2.Day)
AND(Table1 LEFT JOIN Table2 ON (Table1.Component =
Table2.Component) AND (Table1.[Product Code] = Table2.
[Product Code])) LEFT JOIN Table3 ON (Table1.Component =
Table3.Component) AND (Table1.[Product Code] = Table3.
[Product Code])

Does a given record in Table1 match up to two or more records in
Table2, and to two or more records in Table3? If so, you'll get all
possible combinations of records in Table2 and Table3.


.

 

[John Vinson]

Thanks, John.
Yes, it's true.
May be I need to use Recordset to handle with it?

A query IS a Recordset. I.e. that's not going to help.

Step back a bit. What are you going to do with this query when you get
it? What's the goal? I wonder if a Form/Report with
subforms/subreports might be easier than some snarky query!

 

[Alex]

Thanks again, John.

We have recycled product's component identified by product
code and component#. Part of this is being used, part of
this is being thrown out, the rest should be returned back
to inventories. Everything occurs in different time with
different qty.
The goal is to know how much of these different products'
components should be returned back to inventories for the
period.
Thanks.