help with query

[todd]

I have the following table:

Item Assembly Date
comp1 ass1 1/1/03
comp2 ass1 1/2/03
comp3 ass2 1/3/03
comp4 ass2 1/4/03
ass1 n/a 1/5/03
comp5 ass2 1/8/03
ass2 n/a 1/10/03

I want a query to return:

Item Assembly Assembly Date
comp1 ass1 1/5/03
comp2 ass1 1/5/03
comp3 ass2 1/10/03
comp4 ass2 1/10/03
comp5 ass2 1/10/03

So the query would find the assembly date for each
component by matching components with their assembly
numbers and assigning the component the assembly date as
the date of the assembly.

Can someone help?

Thanks.

 

[Michel Walsh]

Hi,



Bring the table twice, link Assembly to Item. The result should be like:

SELECT a.Item, a.Assembly, b.AssemblyDate
FROM myTable As a LEFT JOIN myTable As b
ON a.Assembly = b.Item



Hoping it may help,
Vanderghast, Access MVP