I am sure this is simple BUT. . . . Deduping a table - Distinct . . . But want o

R

Rick

Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have duplicate
records (first name and last name). BUT I want to return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the comparison.
How can I return these other fields without this happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in that
as well!?

Thanks in advance.

Rick
 
T

Tom Ellison

Dear Rick:

Well, you can't eliminate duplications and still see other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from the table.
Sort by the column(s) on which you want to search for duplication. At
this point, you see the duplicates together. The only thing you can
do to this to improve it is to eliminate those rows that aren't
duplicated. This is done with a subquery in the WHERE clause that
counts the number of occurrances of the current row's FirstName /
LastName value and filters to those with more than one row.

If you'll write the query up to the point where you're ready to
eliminate non-duplicated columns, and post that back here, I'll add
the filter to remove non-duplicated rows. That's probably the easiest
way to get your work completed while giving you a way to learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
R

Rick

OK! Simple enough.

SELECT [Original table].Firstname, [Original
table].Lastname, [Original table].Address1, [Original
table].Address2, [Original table].Address3
FROM [Original table]
ORDER BY [Original table].Firstname, [Original
table].Lastname;

Thanks so much for your help!

Rick


-----Original Message-----
Dear Rick:

Well, you can't eliminate duplications and still see other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from the table.
Sort by the column(s) on which you want to search for duplication. At
this point, you see the duplicates together. The only thing you can
do to this to improve it is to eliminate those rows that aren't
duplicated. This is done with a subquery in the WHERE clause that
counts the number of occurrances of the current row's FirstName /
LastName value and filters to those with more than one row.

If you'll write the query up to the point where you're ready to
eliminate non-duplicated columns, and post that back here, I'll add
the filter to remove non-duplicated rows. That's probably the easiest
way to get your work completed while giving you a way to learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have duplicate
records (first name and last name). BUT I want to return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the comparison.
How can I return these other fields without this happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in that
as well!?

Thanks in advance.

Rick
Click to expand...

.
Click to expand...
 
T

Tom Ellison

Dear Rick:

Now that I know the exact table and column names:

SELECT Firstname, Lastname, Address1, Address2, Address3
FROM [Original table] T
WHERE (SELECT COUNT(*) FROM [Original table] T1
WHERE T1.Firstname = T.FirstName AND T1.Lastname = T.Lastname)
1
Click to expand...
ORDER BY Firstname, Lastname;

I will comment that sorting by Firstname first and then by Lastname is
unusual, but you could certainly change that if desired.

The query uses a subquery and aliasing. I mention that so you can
look up these techniques in online help or in any books you may have.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


OK! Simple enough.

SELECT [Original table].Firstname, [Original
table].Lastname, [Original table].Address1, [Original
table].Address2, [Original table].Address3
FROM [Original table]
ORDER BY [Original table].Firstname, [Original
table].Lastname;

Thanks so much for your help!

Rick


-----Original Message-----
Dear Rick:

Well, you can't eliminate duplications and still see other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from the table.
Sort by the column(s) on which you want to search for duplication. At
this point, you see the duplicates together. The only thing you can
do to this to improve it is to eliminate those rows that aren't
duplicated. This is done with a subquery in the WHERE clause that
counts the number of occurrances of the current row's FirstName /
LastName value and filters to those with more than one row.

If you'll write the query up to the point where you're ready to
eliminate non-duplicated columns, and post that back here, I'll add
the filter to remove non-duplicated rows. That's probably the easiest
way to get your work completed while giving you a way to learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have duplicate
records (first name and last name). BUT I want to return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the comparison.
How can I return these other fields without this happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in that
as well!?

Thanks in advance.

Rick
Click to expand...

.
Click to expand...
Click to expand...
 
R

Rick

I realized my error in sorting first name first as soon as
I sent you. I will give it a try and thanks so much for
not only providing a solution be an explanation! I was
searching all over the place, I had thought that there
MUST be some variation in the syntax where I could
seperate the "Distinct" stuff from what I really wanted to
see.

Again, thanks!

Rick
-----Original Message-----
Dear Rick:

Now that I know the exact table and column names:

SELECT Firstname, Lastname, Address1, Address2, Address3
FROM [Original table] T
WHERE (SELECT COUNT(*) FROM [Original table] T1
WHERE T1.Firstname = T.FirstName AND T1.Lastname = T.Lastname)
1
Click to expand...
ORDER BY Firstname, Lastname;

I will comment that sorting by Firstname first and then by Lastname is
unusual, but you could certainly change that if desired.

The query uses a subquery and aliasing. I mention that so you can
look up these techniques in online help or in any books you may have.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


OK! Simple enough.

SELECT [Original table].Firstname, [Original
table].Lastname, [Original table].Address1, [Original
table].Address2, [Original table].Address3
FROM [Original table]
ORDER BY [Original table].Firstname, [Original
table].Lastname;

Thanks so much for your help!

Rick


-----Original Message-----
Dear Rick:

Well, you can't eliminate duplications and still see other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from the table.
Sort by the column(s) on which you want to search for duplication. At
this point, you see the duplicates together. The only thing you can
do to this to improve it is to eliminate those rows
Click to expand...
that
aren't
duplicated. This is done with a subquery in the WHERE clause that
counts the number of occurrances of the current row's FirstName /
LastName value and filters to those with more than one row.

If you'll write the query up to the point where you're ready to
eliminate non-duplicated columns, and post that back here, I'll add
the filter to remove non-duplicated rows. That's probably the easiest
way to get your work completed while giving you a way
Click to expand...
to
learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 19 Jul 2004 07:42:34 -0700, "Rick"

Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have duplicate
records (first name and last name). BUT I want to return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the comparison.
How can I return these other fields without this happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in that
as well!?

Thanks in advance.

Rick

.
Click to expand...
Click to expand...

.
Click to expand...
 
T

Tom Ellison

Dear Rick:

I'm glad you could pick up on this so quickly. You're welcome.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I realized my error in sorting first name first as soon as
I sent you. I will give it a try and thanks so much for
not only providing a solution be an explanation! I was
searching all over the place, I had thought that there
MUST be some variation in the syntax where I could
seperate the "Distinct" stuff from what I really wanted to
see.

Again, thanks!

Rick
-----Original Message-----
Dear Rick:

Now that I know the exact table and column names:

SELECT Firstname, Lastname, Address1, Address2, Address3
FROM [Original table] T
WHERE (SELECT COUNT(*) FROM [Original table] T1
WHERE T1.Firstname = T.FirstName AND T1.Lastname = T.Lastname)
1
Click to expand...
ORDER BY Firstname, Lastname;

I will comment that sorting by Firstname first and then by Lastname is
unusual, but you could certainly change that if desired.

The query uses a subquery and aliasing. I mention that so you can
look up these techniques in online help or in any books you may have.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


OK! Simple enough.

SELECT [Original table].Firstname, [Original
table].Lastname, [Original table].Address1, [Original
table].Address2, [Original table].Address3
FROM [Original table]
ORDER BY [Original table].Firstname, [Original
table].Lastname;

Thanks so much for your help!

Rick



-----Original Message-----
Dear Rick:

Well, you can't eliminate duplications and still see
other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from
the table.
Sort by the column(s) on which you want to search for
duplication. At
this point, you see the duplicates together. The only
thing you can
do to this to improve it is to eliminate those rows that
aren't
duplicated. This is done with a subquery in the WHERE
clause that
counts the number of occurrances of the current row's
FirstName /
LastName value and filters to those with more than one
row.

If you'll write the query up to the point where you're
ready to
eliminate non-duplicated columns, and post that back
here, I'll add
the filter to remove non-duplicated rows. That's
probably the easiest
way to get your work completed while giving you a way to
learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 19 Jul 2004 07:42:34 -0700, "Rick"

Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have
duplicate
records (first name and last name). BUT I want to
return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the
comparison.
How can I return these other fields without this
happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in
that
as well!?

Thanks in advance.

Rick

.
Click to expand...

.
Click to expand...
Click to expand...
 
R

Rick

It worked, And I am running with it! This will be a great
help. I would almost rather have the oppertunity to pick
which record I will be deleting anyway. Now that I see it
it makes sense that what I was trying to do didn't work.
-----Original Message-----
Dear Rick:

I'm glad you could pick up on this so quickly. You're welcome.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I realized my error in sorting first name first as soon as
I sent you. I will give it a try and thanks so much for
not only providing a solution be an explanation! I was
searching all over the place, I had thought that there
MUST be some variation in the syntax where I could
seperate the "Distinct" stuff from what I really wanted to
see.

Again, thanks!

Rick
-----Original Message-----
Dear Rick:

Now that I know the exact table and column names:

SELECT Firstname, Lastname, Address1, Address2, Address3
FROM [Original table] T
WHERE (SELECT COUNT(*) FROM [Original table] T1
WHERE T1.Firstname = T.FirstName AND T1.Lastname = T.Lastname)
1
ORDER BY Firstname, Lastname;

I will comment that sorting by Firstname first and then by Lastname is
unusual, but you could certainly change that if desired.

The query uses a subquery and aliasing. I mention that so you can
look up these techniques in online help or in any books you may have.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 19 Jul 2004 10:06:55 -0700, "Rick"

OK! Simple enough.

SELECT [Original table].Firstname, [Original
table].Lastname, [Original table].Address1, [Original
table].Address2, [Original table].Address3
FROM [Original table]
ORDER BY [Original table].Firstname, [Original
table].Lastname;

Thanks so much for your help!

Rick



-----Original Message-----
Dear Rick:

Well, you can't eliminate duplications and still see
other columns
that may be different. So, here's what i usually do:

Write a query that returns all the desired columns from
the table.
Sort by the column(s) on which you want to search for
duplication. At
this point, you see the duplicates together. The only
thing you can
do to this to improve it is to eliminate those rows that
aren't
duplicated. This is done with a subquery in the WHERE
clause that
counts the number of occurrances of the current row's
FirstName /
LastName value and filters to those with more than one
row.

If you'll write the query up to the point where you're
ready to
eliminate non-duplicated columns, and post that back
here, I'll add
the filter to remove non-duplicated rows. That's
probably the easiest
way to get your work completed while giving you a way to
learn how
this can be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 19 Jul 2004 07:42:34 -0700, "Rick"

Is there an easy way to do this.

I have a table for which I want to run a query that does
not return records where two of the fields have
duplicate
records (first name and last name). BUT I want to
return
other fields other than these two fields (without
comparison based on them).

If I list the other fields in my SELECT, or in this case
SELECT DISTINCT, they are used as part of the
comparison.
How can I return these other fields without this
happening?

I am sure this is SQL 101, forgive me!

What I am trying to do is, I have a table (SLOPPY ONE)
that was given to me. I need to basically "de-dup" it
based on first name and last name. But I need to return
the associated addresses as well.

I know I can achive the first with a simple:

SELECT DISTINCT [Original table].Firstname, [Original
table].Lastname
FROM [Original table];

But if I add the address it throws out duplicates in
that
as well!?

Thanks in advance.

Rick

.


.
Click to expand...
Click to expand...

.
Click to expand...
 
E

Ernie

Not to throw a wrench into your plans or anything, but I
should like to point out that "John Smith, 1 N Some
Street, AnyCity, USA" is NOT the same as "John Smith, 123
Anystreet, SomeCity, Alberta, Canada" (they may not even
be related).

Selecting out duplicates based on just the first and last
name may not be entirely useful.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create a distinct Join 3
Selecting Distinct Memo Fields 1
Queries with Linked Tables 5
Counting distinct? 2
query to show where no data in a stated column 0
DISTINCT changes data to eliminate duplicates? 2
SELECT DISTINCT query is not deduplicating 2
I am trying to write a small query which will remove the duplicate 1

Top