Find like data between tables

[thart21]

This may have already been addressed, but I can't even think of how to word it to do a good search.

I have one table, tblInvoices. I've done a query to search for "Micro*" in my [supplier] field, along with [Item].

I then need to pull from the same tblInvoices, any other records that contain the same [Item] as one that was queried in qryMicro.

I've tried to make a relationship between tblInvoices and qryMicro with [Item] but am getting too many records. I don't want to view records from qryMicro as I'm going to do a Union query to merge the two queries. I've tried
Field: Item Criteria tblInvoices.Item = qryMicro.Item w/o a relationship between the two but it's no better.

Any ideas as to how I can accomplish this?

Thanks,

Toni

 

[petehilljnr]

You're getting too many records because you aren't grouping by the item, therefore you are getting n X n records.

qryMicro should read something like:

select [item]
from tblInvoices
where supplier like "Micro*"
group by [item]

Then in a new query:
select qryMicro.[item], tblInvoices.*
from qryMicro, tblInvoices
where qryMicro.[item] = tblInvoices.[item]