cwbarrett
asked on
Query with unique records based on one field
I am trying to create a query that shows distinct records but only based on one field (I quess it would not be called distinct but I don't know what to call it). But I still need to show the other fields in the record. Adding the other fields to the query just ends up showing all the records.
Tried creating a distinct record query that only contains the one field and combining that with another query but that doesn't work either, still shows all the records, doesn't matter what type of join I make it.
At this time I do not need to edit the records. I am running this query from another application (Word - Mailmerge) so running VBA or a Macro wouldn't work.
Help would be appreciated.
Charlie
Tried creating a distinct record query that only contains the one field and combining that with another query but that doesn't work either, still shows all the records, doesn't matter what type of join I make it.
At this time I do not need to edit the records. I am running this query from another application (Word - Mailmerge) so running VBA or a Macro wouldn't work.
Help would be appreciated.
Charlie
You also have to say how you get the values for the other fields you want to include.
So if you have data like...
F1 F2 F3
1 123 345
1 346 678
1 225 778
You are saying you just want one record for F1=1 ; so which values of F2 and F3 do you choose and why?
So if you have data like...
F1 F2 F3
1 123 345
1 346 678
1 225 778
You are saying you just want one record for F1=1 ; so which values of F2 and F3 do you choose and why?
ASKER
F2 and F3 are irrelevant. Want F1 to show unique values only.
Records
F1 F2 F3
1 123 345
1 346 678
1 225 778
2 222 222
2 333 333
3 123 123
3 321 321
Query Results
F1 F2 F3
1 123 345
2 222 222
3 123 123
or
F1 F2 F3
1 346 678
2 333 333
3 321 321
or
F1 F2 F3
1 225 778
2 333 333
3 321 321
Records
F1 F2 F3
1 123 345
1 346 678
1 225 778
2 222 222
2 333 333
3 123 123
3 321 321
Query Results
F1 F2 F3
1 123 345
2 222 222
3 123 123
or
F1 F2 F3
1 346 678
2 333 333
3 321 321
or
F1 F2 F3
1 225 778
2 333 333
3 321 321
"F2 and F3 are irrelevant"
Then why are they part of the results? Their presence only serves to make the problem more complex.
Then why are they part of the results? Their presence only serves to make the problem more complex.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
You solution works great. Thanks.
Also need to know, based on the data you provide us, what you want the output of the query to look like, so we can make sure we get you a query that works for your purpose.