Removing duplicates from a column in Access query?

Syringer

Lifer
Aug 2, 2001
19,333
2
71
Is there any way to set a criteria within an Access query that basically says not to list itself if it exists elsewhere?

e.g. if I have a table:

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

That either row 3 or 5 will be removed (but not both) because in the second column it contains orange?
 

sivart

Golden Member
Oct 20, 2000
1,786
0
0
How would it know which row to remove? You need some condition to help it decide which row to remove.
 

Syringer

Lifer
Aug 2, 2001
19,333
2
71
Doesn't really matter at this point, I would imagine it just keeps the first one.

So if I can create a criteria like:

iif(number of instances of this cell > 0, remove all but one of them) or something.
 

KLin

Lifer
Feb 29, 2000
29,969
394
126
Good luck figuring that one out. :p. Why do you need to do this?
 

sao123

Lifer
May 27, 2002
12,653
205
106
access querys can do what you ask, its been awhile since ive done it, but give me some time to remember and ill post how to do it.
 

GeekDrew

Diamond Member
Jun 7, 2000
9,099
19
81
Is there any way to set a criteria within an Access query that basically says not to list itself if it exists elsewhere?

e.g. if I have a table:

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

That either row 3 or 5 will be removed (but not both) because in the second column it contains orange?

Do you actually have 3 columns defined on the table, or are all three values in one single column?
 

sao123

Lifer
May 27, 2002
12,653
205
106
Is there any way to set a criteria within an Access query that basically says not to list itself if it exists elsewhere?

e.g. if I have a table:

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

That either row 3 or 5 will be removed (but not both) because in the second column it contains orange?


ok, access will only hide the duplicates if the entire record is a duplicate, not just 1 field.
Perhaps what you should actually be doing is query based on the items you have duplicates on and only accept the first record.
 

DT4K

Diamond Member
Jan 21, 2002
6,944
3
81
I'm trying to figure out why you would want to do that.
If the data in the other columns is not important and you don't care if it's missing, than why are you even querying it?

You said that you don't care if 3 or 5 gets removed. So it sounds like what you are saying is that you don't care about horse, john, mouse, and mike. Either you don't care about them or they don't have any relationship to the other data in the same record.
And if that's true, then why are those columns in your query?
 

KLin

Lifer
Feb 29, 2000
29,969
394
126
I'm trying to figure out why you would want to do that.
If the data in the other columns is not important and you don't care if it's missing, than why are you even querying it?

You said that you don't care if 3 or 5 gets removed. So it sounds like what you are saying is that you don't care about horse, john, mouse, and mike. Either you don't care about them or they don't have any relationship to the other data in the same record.
And if that's true, then why are those columns in your query?

I asked for a why as well, but never got an answer. Explaining why the OP wants to do this might help someone give the OP a good solution.
 

leeland

Diamond Member
Dec 12, 2000
3,659
0
76
so you have a total of 4 columns? and you want to evaluate column 2 to 3 to 4? to find any duplicates across those 3 columns? and only show one of the columns, preferably the first detection?
 

sao123

Lifer
May 27, 2002
12,653
205
106


well unfortunately this will NOt do what you originally asked in the OP...

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

NONE of these rows would be removed... because the ENTIRE ROW has to be identical for the removal to take place. Thus a DUPLICATE RECORD, not just a DUPLICATE VALUE in a RECORD FIELD.


which is exactly what i said in my last post.
 
Last edited:

DT4K

Diamond Member
Jan 21, 2002
6,944
3
81
well unfortunately this will NOt do what you originally asked in the OP...

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

NONE of these rows would be removed... because the ENTIRE ROW has to be identical for the removal to take place. Thus a DUPLICATE RECORD, not just a DUPLICATE VALUE in a RECORD FIELD.


which is exactly what i said in my last post.

Yeah, the article is talking about actually removing duplicate records from a table. If you just want your query to leave out duplicate records, you use the word DISTINCT. But as SAO pointed out, that will have no effect on your table because the list you provided doesn't contain any duplicate records.

Putting aside any of the details about how to accomplish what you are asking, the real question is still WHY?

Like I said, if you don't care about horse, john, mouse, and mike, then why are you putting those fields in your query.

The only thing I can think of is that maybe what you really want is just a list of all the fruits, without duplicates. If this is the case, you can use DISTINCT, but you need to only include the column with the fruits in your query, and not the columns with the names and animals.

In other words, if you do a
SELECT DISTINCT [Fruit] FROM WhateverTable, then the two records with "Orange" do become duplicate records and DISTINCT will eliminate the duplicates.

But if you do a
SELECT DISTINCT * FROM WhateverTable, then the two records with "Orange" are NOT duplicates and won't be removed by DISTINCT.
 

Syringer

Lifer
Aug 2, 2001
19,333
2
71
well unfortunately this will NOt do what you originally asked in the OP...

1. dog apple tim
2. cat banana joe
3. horse orange john
4. elephant grape will
5. mouse orange mike
6. lion pear brian

NONE of these rows would be removed... because the ENTIRE ROW has to be identical for the removal to take place. Thus a DUPLICATE RECORD, not just a DUPLICATE VALUE in a RECORD FIELD.


which is exactly what i said in my last post.

Nope, it removed them all. You just have to set col B above as having the primary key, and once you append the data in the structure, it reads col B as the primary key and removes all duplicates within that column (since you can't have duplicate primary keys).
 
Last edited:

Syringer

Lifer
Aug 2, 2001
19,333
2
71
And as for why it's in the query, the query I'm running is based on a table that has thousands of rows of product information, and they'll have columns of primary SKUs and then variations of that.

For instance in a table that has clothing items, one column will have the SKU: ABC listed 5 times, because there are color/size variations, and that column will have child SKUs of ABC-Black-L, ABC-Brown-L, ABC-Brown-XL, etc. so I just want to keep the unique primary skus (or "parent SKUs).

Here's a brief synopsis of what we're doing: http://forums.anandtech.com/showthread.php?t=2020970&highlight=
 

DT4K

Diamond Member
Jan 21, 2002
6,944
3
81
So like I said, you don't care about columns A and C. You really just want a list of the distinct values in column B.
I know you already did what you needed to do (although in a much more complicated way than necessary).

But basically, you just need:

SELECT DISTINCT B
FROM [whatevertable]