Duplicate Query Search with Delete on certain

[coolcatkelso]

Hi guys

Not posted for a long time and don't have permissions to post in Querys Faq.

I want to be able to run a duplicate query on ProductID, it usually has 2 or 3 same ID's as somethings come through as special offer.

I want to be able to run the query to search for the duplicates and delete on the Lowest Priced one only?

Is this something that is possable?

THe fields are setout like:

ID: Name: Price: Description:

23 Fish1 £2.99 Guppy Fish
23 Fish1 £1.80 Guppy Fish

You get the idea, I want to be able to delete just the lowest price ones only. There are about 1,500 records in total so doing it manually would take a while

 

[jdraw]

Can you show us the table layout?

It is common to uniquely identify records in tables with a Primary key. This will prevent duplicates.

You can try the Access query wizard - it has a Duplicates query, that you could start with, and modify accordingly.

Good luck.

 

[coolcatkelso]

Access has appointed its own indexed I'd called I'd, the one that has duplicate entry's is my product ref.

The Id is unique, product ref isn't.

So I need something like

Delete all
From mytable field: product ref & price
Where price is lower

?

 

[coolcatkelso]

Forgt to mention that the table source is imported from an excel doc

 

[jdraw]

I don't think the table source is involved.

I'm not sure why you have duplicates in your table design. Usually records in tables are uniquely defined by means of a Primary Key.

In any event, I have mocked up your situation based on my understanding of your post.
See attached jpgs

Here is the sql for query45 the select query to identify records to be deleted.

SELECT TestDelete.ID
, TestDelete.sName
, Min(TestDelete.Price) AS MinOfPrice
FROM TestDelete
GROUP BY TestDelete.ID, TestDelete.sName;

 

[coolcatkelso]

That looks perfect thank you.

I didn't setup the excel price list, this is sent to me by the supplier so it is there ref numbers. Went I imported into access I created a new I'd field which is set to primary key


Thank you again

 

[coolcatkelso]

Added that sql into the query and works great, only problem... I cant delete the records that I select.. The option to delete is greyed out?

 

[jdraw]

I'm using 2010 - Did you Enable the database?
Do you have something set as read only?

Are you working with a linked excel table?