Need to remove duplicates and keep only the highest Status .

[DBGuy11]

I have a table that houses information on supplies. Each supply has its own
stock number and status code. I have duplicate and triplecate stocknumbers in
this table, sometimes with different status codes, sometimes with identical
status codes. I have to remove all duplicate records from my table and only
keeping the records with the higher status code. Here's a sample;
Stock# Status Code
1234 TA
1234 TB
1234 TC
5678 TA
5678 TC
5678 TC

The Status Code hierarchy is TA is higher than TB which is higher than TC. I
need to keep the duplicated record with the highest Status Code. Can I do
this in a query? If not then how. Thanks in Advance.

 

[Michel Walsh]

Hi,


SELECT StockNumber, MIN(statusCode)
FROM myTable
GROUP BY StockNumber



even if for you TA is 'higher' than TB, alphanumerically, the ordering is
reversed, so you need the MIN( ) aggregate, not the MAX( ).


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


SELECT [Stock#], MIN( [Status Code] )
FROM myTableNameHere
GROUP BY [Stock#]



is the syntax to use if your fields name are really as you said. Being
"illegal" names, they have to be surrounded by [ ] .



Vanderghast, Access MVP