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
Find matches in table based on 2 fields
-
- 4StarLounger
- Posts: 579
- Joined: 10 Feb 2010, 17:32
Find matches in table based on 2 fields
You do not have the required permissions to view the files attached to this post.
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
Re: Find matches in table based on 2 fields
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."
You do not have the required permissions to view the files attached to this post.
Charlotte
-
- 4StarLounger
- Posts: 579
- Joined: 10 Feb 2010, 17:32
Re: Find matches in table based on 2 fields
Thank you Charlotte - I changed the query to inner joins, and it returns all records.
Did I miss something?
Michael
Did I miss something?
Michael
You do not have the required permissions to view the files attached to this post.
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
Re: Find matches in table based on 2 fields
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?
Charlotte
-
- 4StarLounger
- Posts: 579
- Joined: 10 Feb 2010, 17:32
Re: Find matches in table based on 2 fields
(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 )Charlotte wrote: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?
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
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
Re: Find matches in table based on 2 fields
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.
You do not have the required permissions to view the files attached to this post.
Charlotte
-
- 4StarLounger
- Posts: 579
- Joined: 10 Feb 2010, 17:32
Re: Find matches in table based on 2 fields
Excellent ! Thank you so much Charlotte - it's perfect.Charlotte wrote: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.
It just got cooler here in Sacramento !!
Michael