distinct records

Hey John,

I ran this query and logically it made sense:
SELECT *
FROM corr12
WHERE contact in (select distinct contact from corr12)

It ran, but all records were returned (meaning the duplicates were not
eliminated). Can I alter this somehow to do what I want it to do? Thanks!

"John W. Vinson" wrote:

On Thu, 10 May 2007 14:02:00 -0700, Matt
wrote:

Basically I have a list of users in this table. Some of the records repeat
exactly. I want to eliminate duplicates and have a new table out of these
duplicates to use exporting. The problem is, the timestamp makes these user
dupes unique so I can't just select distincts into a new table. I want all
fields of all records where the name and/or phone numbers are uniqe. Because
of the timestamp the records overall are distinct, but dupes are the same
users. Thanks John!


Ok... did you try my suggestion?

You can use a Totals query grouping by the two fields, and using First as the
"total" function for the remaining fields, to pick one (arbitrary, first in
disk storage order) record for each pair of values.

You haven't posted your table or fieldnames so I can't write the query for
you, but this should work.

John W. Vinson [MVP]

On Fri, 11 May 2007 10:26:00 -0700, Matt
wrote:

Hey John,
Sorry for the confusion. Basically what I was saying was I just want Contact
to be used at the reference for what is distinct. I don't want to compare
entire records for uniqueness, just Contact. I do want the results to include
the entire record though. Can I somehow get you an attachment with the table
on it? I don't know how to format in this box.

Ok.

You have six records for a Contact.

They may or may not have different values for Firm, Subpr, Segment, Create,
CSM, Phone, and Type. There's certainly nothing (that I can see) in the design
of the database that would suggest that these will be different.

Which of the six records do you want to see? I presume Create is the
timestamp; if you don't want to see that you could leave it out - but what if
some of the OTHER fields are different?

The values are distinct - so you want to see them - or they're identical. But
*THERE IS A CONTRADICTION* if you say you want to see only one record for each
contact, but you want to see all of these fields. You have to give on one or
the other requirement!

John W. Vinson [MVP]

On Fri, 11 May 2007 10:26:00 -0700, Matt
wrote:

Hey John,
Sorry for the confusion. Basically what I was saying was I just want Contact
to be used at the reference for what is distinct. I don't want to compare
entire records for uniqueness, just Contact. I do want the results to include
the entire record though. Can I somehow get you an attachment with the table
on it? I don't know how to format in this box.

Actually, Matt, I need to leave town for a few days and probabably won't be
able to continue this thread. Perhaps you should repost *with sample data* - a
few records of your table, and the desired output - as a new thread.

John W. Vinson [MVP]