Need to remove duplicates and keep only the highest Status .

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

Guest

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.
 
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
 
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
 
Back
Top