Delete duplicate records if field is not minimum

[gaker10]

Can't find this specific topic, but I load in prices for parts using a query and linked Excel. Sometimes, I pull in parts with the same part number, quantity (price break), and unit of measure. Depending on the competition, the unit price will be different. I only care about the cheapest unit price of course. So how would I set up my criteria to find the minimum of duplicate records (by part number), and delete all other related records?

I already have my duplicate record finder based on Part number, quantity, and unit of measure (in case of bulk items):



In (SELECT [PartNumber] FROM [Pricing] As Tmp GROUP BY [PartNumber],[Quantity],[UOM] HAVING Count(*)>1 And [Quantity] = [Pricing].[Quantity] And [UOM] = [Pricing].[UOM])

Now I want to take the results of this query, and for all records where part numbers are equal (because this query finds ALL parts where there are duplicate quantities and UOM), delete all from my Pricing table if the unit price is not the smallest out of the lot. I feel like this is easier than I'm making it out to be, but I've never used Min/Max functions in Access so I'm unfamiliar with the syntax/usage.

[JoeM]

Question: If you are able to get the minimum values/records you want using your queries, why is it necessary to go back and delete the other records from the original tables? Do you really need to do that at all?

[gaker10]

Because I don't want the other records in my database. I only want records where certain fields match except the unit price field, and if there's 5 parts with matching fields and different unit prices, I only want the cheapest one in the table. If I can modify my query to pull in only the cheaper unit price records, I'll do that too but I don't know how.

[JoeM]

You should just be able to create an Aggregate Query, where you "Group By" all the fields you are using to determine if a record is a duplicate or not, and then by using the "MIN" aggregate function on your price field. This will give you the minimum price for number.

What I was implying is that if you have this, you can use this query as the source for anything else you need to do (Reports, Exports, etc). So then the fact that the underlying table has other "extra" records, that really shouldn't matter, since you are not using the underlying table for anything. So it may not be necessary to remove the other records at all.

If you really want to delete the other records, you would just delete the records from your table that are not found in your aggregate query.

[gaker10]

The table is being used for looking up prices by someone who only wants the cheapest prices in the table and does not know Access well.

[JoeM]

A general rule of thumb with Access is that users (especially inexperienced ones) should NEVER be going into the underlying tables. Access should be controlled through Forms where you give them easy access to just the objects you want them to.

So they should be using the Query, or a Form or Report based on the Query to look up the prices, not the underlying Table.

[gaker10]

This table is controlled through a form, but the user in question only wants certain data, as I described above.

[gaker10]

Not to mention that the database in question is going to get really big really fast. Having 20 (or more) of the same part with different unit prices and the same quantity is basically a waste of time and space.

[JoeM]

This table is controlled through a form, but the user in question only wants certain data, as I described above.

Create a form that that uses the Aggregate Query as it source, instead of the table.

Not to mention that the database in question is going to get really big really fast. Having 20 (or more) of the same part with different unit prices and the same quantity is basically a waste of time and space.

I mentioned how you could delete the records, if you want. But first you need to create your Aggregate Query anyway. If you need help with the deletion part, post the SQL code of your Aggregate Query after you create it. That will let us know what the key fields are and assist in creating the Delete Query.

[gaker10]

SELECT Pricing.PartNumber, Pricing.Quantity, Pricing.UOM, Min(Pricing.[Unit Price]) AS [MinOfUnit Price], Pricing.[Date Of Quote], Pricing.[PriceValidity(Days)], Pricing.ExpirationDate
FROM Pricing
GROUP BY Pricing.PartNumber, Pricing.Quantity, Pricing.UOM, Pricing.[Date Of Quote], Pricing.[PriceValidity(Days)], Pricing.ExpirationDate;

Code for the query. I understand somewhat how it works now, but how can I display a couple more fields without them being part of the query itself?

[gaker10]

A Dlookup I suppose hehe.

[JoeM]

Code for the query. I understand somewhat how it works now, but how can I display a couple more fields without them being part of the query itself?

Are the fields part of the Pricing table? If so, why not just add them to the query (and "Group By" them too)?

If they are part of another table, link the two tables together in your Aggregate Query and add the fields to the Query. Note that if there is not a perfect 1-1 match between the two tables you may want to change the Join Type to a Left Join.

[gaker10]

Yes they are. I dont want them in the query because if Supplier1's price is cheaper than Supplier2's price, I dont care to see Supplier2's price. But, I do need to know where my cheap prices are coming from.

[JoeM]

Yes they are. I dont want them in the query because if Supplier1's price is cheaper than Supplier2's price, I dont care to see Supplier2's price. But, I do need to know where my cheap prices are coming from.

Gotcha.

I usually create a new query in which I link my Aggregate Query back to the Original table on ALL the fields (including price), and then return those other fields I need.

By the way, I am not sure if you use an Autonumber field (or have some other unique field) in your table, but if you do, it would make deleting out those other records you don't want much easier. When you create this other query I just mentioned above, return the unique field too. Then when you are creating your Delete Query, you would just delete all the records from your original table where the unique field value is not found in this query.

[gaker10]

Got it. I do have an autonumber, but Ill just leave the deleting up to the user using the form. Thank you!