another ? about using distinctrow (or some alternative)

  • Thread starter Thread starter BRC
  • Start date Start date
B

BRC

Hi all
I have a query that I would like to eliminate records where one field
may have duplicate values. I was of the impression that there was
something i could put in the "criteria" field in the query design view
that would eliminate duplicate records for the field. I have tried
"unique" , "distinct" "count(*)=1" ect. but none of these seem to
work. Is this more complicated than I think? Any suggestions would be
appreciated. BRC
 
Hi all
I have a query that I would like to eliminate records where one field
may have duplicate values. I was of the impression that there was
something i could put in the "criteria" field in the query design view
that would eliminate duplicate records for the field. I have tried
"unique" , "distinct" "count(*)=1" ect. but none of these seem to
work. Is this more complicated than I think? Any suggestions would be
appreciated. BRC
Click to expand...

You can get this result in a couple of ways. What if there are records which
are duplicate for field A, but *different* for fields B, C and D? Do you want
to see just one record - and if so, which?

If you just want to see an arbitrary record, create a Query based on the
table. Change it to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By on the fields which identify
the group of which you wish to see one example; select "First" for the
remaining fields. You'll see the first record in disk-storage order (an order
which you can't control so it's basically arbitrary).

John W. Vinson [MVP]
 
You can get this result in a couple of ways. What if there are records which
are duplicate for field A, but *different* for fields B, C and D? Do you want
to see just one record - and if so, which?

If you just want to see an arbitrary record, create a Query based on the
table. Change it to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By on the fields which identify
the group of which you wish to see one example; select "First" for the
remaining fields. You'll see the first record in disk-storage order (an order
which you can't control so it's basically arbitrary).

John W. Vinson [MVP]
Click to expand...

John
Thank you, your solution did what I was looking for. The table has
several duplicate records for the address field. What I wanted was a
list of unique addresses. Thanks again.
 
Back
Top