Access query, find highest number for unique value
Posted on 2010-11-12
As is, the following query finds all records in a table which have the highest value in field Mod. That works fine as is.
SELECT tbl1.fld1, tbl1.SKU, Max(tbl1.[Mod]) AS MaxOfMod, tbl1.fld4, tbl1.fld5, tbl1.fld6, tbl1.fld7, tbl1.fld8
GROUP BY tbl1.fld1, tbl1.SKU, tbl1.fld4, tbl1.fld5, tbl1.fld6, tbl1.fld7, tbl1.fld8
HAVING (((Max(tbl1.[Mod]))=(select max([Mod]) from tbl1)) AND ((tbl1.fld6)>0));
Now, in addition to those records and in the same query, I need to find the highest value in field Mod for SKU. A SKU can be in the table multiple times, and I need to see the record which has the highest Mod number.
So, the query as is finds all records that have the highest Mod number existing in field Mod. But, there are other SKU’s in the table which have as their Mod number, say, 7. That SKU may also have a record with a Mod number of 4. I just need to see the record with the Mod number of 7, in addition to all the records the query already shows.
Thanks very much.