Eliminate specific duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2000 on Windows XP. I have a table with two columns, A and B. When I first get the table, Column A contains duplicate values and Column B does not. I need to eliminate the duplicate values in Column A, using Column B as a qualifier; so, if I have two fields showing "pantaloons" in Column A, the first one with 1001 in Column B and the second with 2001 in Column B, when I eliminated all duplicates I would be left with the "pantaloons" with the 2001 value in B.

I have no problems just eliminating duplciates using the method described in Access Help, but I can't find a way to specify which of the duplicate fields to keep. Thanks for helping!
 
If you wish to remove all duplicates except the one(s) with
the highest value in Column B for any given value in
coulmnA, the SQL should be along the lines of
DELETE FROM YourTable T1
WHERE columnB NOT IN (SELECT Max(columnB) FROM YourTable T2
WHERE T2.columnA = T1.columnA)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I'm using Access 2000 on Windows XP. I have a table with
Click to expand...
two columns, A and B. When I first get the table, Column A
contains duplicate values and Column B does not. I need to
eliminate the duplicate values in Column A, using Column B
as a qualifier; so, if I have two fields showing
"pantaloons" in Column A, the first one with 1001 in Column
B and the second with 2001 in Column B, when I eliminated
all duplicates I would be left with the "pantaloons" with
the 2001 value in B.
I have no problems just eliminating duplciates using the
Click to expand...
method described in Access Help, but I can't find a way to
specify which of the duplicate fields to keep. Thanks for
helping!
 
Back
Top