Find matches in table based on 2 fields

[Michael Abrams]

I can't figure this easy one out. Tryong to extract records where 2 fields equal each other.

Pseudocode:
Records where MRN match AND MATCH matches.

The results I want are:
MRN MATCH NAME
123 ABC ME
123 ABC SHE
345 JKL SHE
345 JKL THEM
678 XYZ WE
678 XYZ WERE

Thank you so much.

Michael

[Charlotte]

All you need do is change the outer joins to inner joins by going into the query in design view, double-clicking each line joining the two tables, and change the join property to "Only include rows where joined fields from both tables are equal."

[Michael Abrams]

Thank you Charlotte - I changed the query to inner joins, and it returns all records.

Did I miss something?

Michael

[Charlotte]

No, I'm the one who wasn't paying attention. Let me ask you first, what are you trying to accomplish with this? The fields are all text fields and you have no indexes on them. What is the actual problem you're trying to solve? And why did you use a Group By query for this?

[Michael Abrams]

No, I'm the one who wasn't paying attention. Let me ask you first, what are you trying to accomplish with this? The fields are all text fields and you have no indexes on them. What is the actual problem you're trying to solve? And why did you use a Group By query for this?

(I used a stripped down version of the table -there are actually 30,000 records and 20 other fields not related to what I need to do )

There are pairs of records - every pair has a unique "MATCH" in the MATCH field.
Of these pairs, some have the same MRN, some have different MRN. (The NAME field is irrelevant for this query)

I need to extract only the pairs of records where the MATCH number is the same, and the MRN is the same.

So looking at the small table, I need the results as listed in the original post.

Once I can extract the pairs of records that have the same MATCH & MRN, we process them further.

Thanks for sticking with me !

As for why did I use a Group By query? Because I tried about 5 or 6 different queries, none worked, so I left one hoping it was close and just needed a tweak.

Michael

[Charlotte]

Try Query2 in the attached database and see if this is what you were looking for. It returns only the records where there are more than one with a combination of a particular MRN and MATCH value.

[Michael Abrams]

Try Query2 in the attached database and see if this is what you were looking for. It returns only the records where there are more than one with a combination of a particular MRN and MATCH value.

Excellent ! Thank you so much Charlotte - it's perfect.

It just got cooler here in Sacramento !!

Michael