MS Access - Identify duplicates having minimum value

Question

I have a MS Access query which pulls [product name] and [price] along with several other fields. My problem is that I have multiple instances where [product name] is duplicated and [price] may or may not be the same between the duplicates. I'd like the query to show only one record for each of the duplicates and the minimum [price] associated with that [product name]....one big master list with no duplicates in it..... the final list should include all [product names] that didn't have duplicates and their associated price also.

I know this should be simple but for whatever reason I'm beating my brains out on it. I tried using a crosstab query already to return the minimum values for each unique [product name] but I have so many records that the crosstab query errors out on column count.

Any help would be greatly appreciated.

AC

possible duplicate of http://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value — animesh suman, Oct 19 '15 at 03:34
Have you tried the query wizard? It will set up a "find duplicates" query for you. — Dan, Oct 19 '15 at 15:58

score 0 · Answer 1 · answered May 19 '16 at 23:47

MSAccess doesn't support Partition functions.

However, if you were able to get your data into a SQL database, you could easily do this using Window/Partition functions, which would allow you to do interesting things like select the cheapest 2 products, etc

 SELECT *
 FROM
 (
     SELECT ROW_NUMBER() 
          OVER (PARTITION BY [ProductName] ORDER BY [Price]) as RowNum, *
     FROM Table
 ) X 
 WHERE RowNum = 1

You can now correctly get the actual row that was identified as the one with the lowest price and you can modify the ordering function to use multiple criteria, such as "Show me the latest product which had the cheapest score", etc.

MS Access - Identify duplicates having minimum value

1 Answers1