Query Comparison

[evr72]

Hello,

I have two table one called items and the other itemscomp

my items table contains all my items it only has 3 fields

Itemnum, component and qty this shows something like this

Itemnum     component       qty
0001        abc              .96
0001        xyz              .04
0045        abc              .75
0045        bb2              .25

the itemscomp has the same exact fields, but the item numbers are different

Itemnum     component         qty
TM01         abc              .96
TM01         xyz              .04
ASM5         abc              .75
ASM5         bb2              .25

what I am trying to do is get all the part numbers who's components are the same as the "itemscomp" table


I did a relational query

SELECT [items].itemnum, [items].component, [items].qty, itemscomp.itemnum, itemscomp.component, itemscomp.qty
FROM itemscomp LEFT JOIN [items] ON itemscomp.component = [items].component

But this gives me something like this

Itemnum     component       qty	
0001        abc              .96	TM01         abc              .96
0001        abc              .96	ASM5        abc              .96
0045        abc              .75	TM01         abc              .96
0045        abc              .75	ASM5         abc              .75

and I would like to get something like this

0001        abc              .96	TM01         abc              .96
0001        xyz             .04	TM01         xyz              .04
0045        abc              .75	ASM5         abc              .75
0045        bb2              .25	ASM5         bb2              .25

any help is much appreciated.

 

[dhookom]

What happens if you also join on the Qty fields?

Duane
Hook'D on Access
MS Access MVP