distinct records

[Guest]

Hi Folks,
I am trying to query one table for distinct records based on only 2 fields.
I want all the columns returned, but I only want to use 2 columns to be
referenced for unqiueness. I could reference the whole record for
distinctness but the timestamp is of the clock's time is still distinct, BUT
they are the same user. So I want to reference their name and phone number
only yet still return all the rows and have the results put into a new table.
Here is my idea for the SQL:

SELECT * INTO Table1
FROM Table
WHERE (basically this is where I would want to say where the contact name
and phone numbers are distinct)


Can I do this in Access 2003 updated? Thanks!

 

[John W. Vinson]

Hi Folks,
I am trying to query one table for distinct records based on only 2 fields.
I want all the columns returned, but I only want to use 2 columns to be
referenced for unqiueness. I could reference the whole record for
distinctness but the timestamp is of the clock's time is still distinct, BUT
they are the same user. So I want to reference their name and phone number
only yet still return all the rows and have the results put into a new table.
Here is my idea for the SQL:

SELECT * INTO Table1
FROM Table
WHERE (basically this is where I would want to say where the contact name
and phone numbers are distinct)


Can I do this in Access 2003 updated? Thanks!

If you have multiple records, with different values of some fields, do you
care *which* record you're pulling?

And why do you want to store this data redundantly in a second table? What
will you do with it that you can't do with a Query?

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.

John W. Vinson [MVP]

 

[Guest]

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!

 

[knightconsulting]

step 1: create a query without the timestamp that returns what you
want, save it. step 2: create a new query, bring in the table with all
the fields that you want and join it to the query you created in step
one so that it only returns records that are in your step 1 query.

 

[Guest]

Hey Knight!

The timestamp is part of the field is the problem. The dates or clock times
can be different per record. The field is a creation time of that record. So
if the user who entered themselves into the DB via a form did it more than
once, the clock does not allow the records to be exact duplicates. Are you
saying use some kind of join SQL method? Thanks, and sorry, I am a newbie at
querying and I am forced to work with this table because they want all the
fields. Basically, I want a table that has all the users to appear just once
even if they appear more than once in the table I am working with regardless
of differing creation field times. Thanks again!

 

[John W. Vinson]

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]

 

[Guest]

Sorry John, as I mentioned, I am a newbie for sure at this so I was waiting
to look into this when I got back to the DB in the morning. But I will
definitely pass along that information to you if you don't mind helping me
with the query for sure. I don't know the Totals or First commands either.
Thanks a lot and I will get this up here in about 12 hours! Thanks again!

 

[Guest]

Hey John,

My table is called CORR12. the columns are Firm, Subpr, Segment, Create,
CSM, Contact, Phone, Type. Now I want to return all distinct rows on the
basis of Contact, not entire records. Do you need example data? There are no
other tables involved. By the way I am using updated 2003. Thanks John!

 

[John W. Vinson]

Hey John,

My table is called CORR12. the columns are Firm, Subpr, Segment, Create,
CSM, Contact, Phone, Type. Now I want to return all distinct rows on the
basis of Contact, not entire records. Do you need example data? There are no
other tables involved. By the way I am using updated 2003. Thanks John!

I guess I don't understand what you mean by "all distinct rows on the basis of
Contact, not entire records". A four or five row example with the data in your
table and your desired output would certainly be helpful!

John W. Vinson [MVP]

 

[Guest]

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

 

[Guest]

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]

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]

 

[John W. Vinson]

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]