Delete specific records from a Find Duplicates Query

[scorpio]

I ran a Find Duplicates Query on my table.

The duplicate records have the same ID #, Location & Date. The only difference is the price so I would like to delete only the record that has the lowest price for each duplicate. I attached sample data.





Thank Youtest.doc

[June7]

Field names in post not same as in the example document. Date is a reserved word. Should not use reserved words as names.

Try:

DELETE FROM table WHERE Price=DMin("Price", "table", "ID=" & [ID] & " AND Location='" & [Location] & "' AND [Date]=#" & [Date] & "#") AND DCount("*", "table", "ID=" & [ID] & " AND Location='" & [Location] & "' AND [Date]=#" & [Date] & "#")>1

Assumes ID is number type and Location is text type. But the example document suggests ID_Number is text and MTh is a number not a date field.

DELETE FROM table WHERE _Price=DMin("_Price", "table", "ID_Number='" & [ID_Number] & "' AND _Site='" & [_Site] & "' AND MTh=" & [MTh]) AND DCount("*", "table", "ID_Number='" & [ID_Number] & "' AND _Site='" & [_Site] & "' AND MTh=" & [MTh])>1

Test with a copy of table first.

[scorpio]

THe field names in the example document are the correct names, my apologies.

Also, ID cannot be a number type because some of the ID numbers contain letters as well.

Where do I enter the information you provided me above? I dont understand your answer. It seems complicated because I am not advanced with Access.

Also, can I just add a column in the query and label one price as min and one as max. Then set the criteria to show only the records labeled min number based on price and then delete those records?

Is it possible to just delete these particluar records from the query versus the table? I dont need them for this project but I may need these records for a different project.

Thank You

[June7]

What I show are SQL statements. You can copy/paste into SQL View of query builder then switch to Design View.

I should have suggested just filtering and not deleting records. Deleting records should rarely need to be done but I assumed you had a good reason for deleting.

Used domain aggregate functions (DSum, DMin, DMax, etc). Alternative could possibly use nested subquery. Review http://allenbrowne.com/subquery-01.html#TopN

Consider:

Query1:
SELECT _Site, MTH, Min(_Price) AS MinPrice, Max(_Price) AS MaxPrice FROM tablename GROUP BY _Site, MTh;

Now build another query that uses the table and Query1 joined on the _Site and Mth fields. Filter criteria under _Price field: =MaxPrice

[scorpio]

test.docI do need to delete the record with the min value of the two duplicates but I only want to delete it from the query I found the duplicates in not the table. Is this possible? I need to delete this record because I have to create an inner join from another query and I dont want it to pull in both of the duplicate records.

Also, I understand to enter the SQL statement under the SQL view of the query but where do I insert it if there is already and SQL statement present?

I included another attachement so you can see my query in design, sql and datasheet view.

Also, do you know of any good sites for Access tutorials in general and on queries that go into detail?

Thank You

[June7]

Why would there already be an SQL statement? Why are you not in a new query? You can delete whatever is there and replace it.

Did you try my last suggestion?

There are lots of web tutorials.

http://allenbrowne.com/subquery-01.html

http://www.w3schools.com/sql/default.asp

[scorpio]

test.docThe suggestion you made is not giving me the results I need. I did create a new query based off the duplicates query results and deleted what was there and entered what you suggested. I attached the results so you can see.

I need the lowest component price for the two duplicate records with the same item number. I think your suggestion is only basing it off of site and reporting period? Also, I only want this peformed on my results from my find duplicate query.

For example:

Record A item # 12345 price $1.00
Record B item # 12345 price $$.50


I want my results to only show me the record for
A - item # 12345 price $1.00

[June7]

This is very confusing. There will be no more than 2 'duplicates'? A record is 'duplicate' if the item/period/site are the same? Yet you include PO_Number in the GROUP BY. That field not previously mentioned. How does PO_Number pertain?

What field is the item number - ID_Number? Should my suggested query include ID_Number? The grouping needs to be the fields that make the records 'duplicate'.

Query1:
SELECT ID_Number, _Site, MTH, Min(_Price) AS MinPrice, Max(_Price) AS MaxPrice FROM tablename GROUP BY ID_Number, _Site, MTh;

The Price field should not be GROUP BY, it should be Min or Max.

My suggestion was to use the table, not the duplicate query.

[scorpio]

The item #, period, site, & po # are all the fields that make the record a duplicate. The only different field is the price.

When I put the Price field as Min & Max it returns results of more records versus just the duplicate records from my find duplicates query. & it doesn't return the correct value for the min field.

I will run it on the table & see what my results are.