Query Hanging - a little help Pleass

[PeterTaylor]

I am using Access 2013 and a novice. I have a database with ~1,000,000 that I am trying to remove the duplicates from. Using the query wizard I have created the following Query
Code:

SELECT All_Soils.LAT, All_Soils.LONG, All_Soils.Au_ppb, All_Soils.Ag_ppm, All_Soils.As_ppm, All_Soils.Cu_ppm, All_Soils.Ni_ppm, All_Soils.Co_ppm, All_Soils.Pb_ppm, All_Soils.Zn_ppm, All_Soils.ID
FROM All_Soils
WHERE (((All_Soils.LAT) In (SELECT [LAT] FROM [All_Soils] As Tmp GROUP BY [LAT],[LONG],[Au_ppb],[Ag_ppm],[As_ppm],[Cu_ppm],[Ni_ppm],[Co_ppm],[Pb_ppm],[Zn_ppm] HAVING Count(*)>1  And [LONG] = [All_Soils].[LONG] And [Au_ppb] = [All_Soils].[Au_ppb] And [Ag_ppm] = [All_Soils].[Ag_ppm] And [As_ppm] = [All_Soils].[As_ppm] And [Cu_ppm] = [All_Soils].[Cu_ppm] And [Ni_ppm] = [All_Soils].[Ni_ppm] And [Co_ppm] = [All_Soils].[Co_ppm] And [Pb_ppm] = [All_Soils].[Pb_ppm] And [Zn_ppm] = [All_Soils].[Zn_ppm])))
ORDER BY All_Soils.LAT, All_Soils.LONG, All_Soils.Au_ppb, All_Soils.Ag_ppm, All_Soils.As_ppm, All_Soils.Cu_ppm, All_Soils.Ni_ppm, All_Soils.Co_ppm, All_Soils.Pb_ppm, All_Soils.Zn_ppm;

This works well quickly identifing about 35,000 records that have duplicates. Now I want to keep the first duplicate and delete the rest. After some reading around I have come up with the following:
Code:

SELECT All_Soils.LAT, All_Soils.LONG, All_Soils.Au_ppb, All_Soils.Ag_ppm, All_Soils.As_ppm, All_Soils.Cu_ppm, All_Soils.Ni_ppm, All_Soils.Co_ppm, All_Soils.Pb_ppm, All_Soils.Zn_ppm, Max(All_Soils.ID) AS MaxOfID
FROM All_Soils
GROUP BY All_Soils.LAT, All_Soils.LONG, All_Soils.Au_ppb, All_Soils.Ag_ppm, All_Soils.As_ppm, All_Soils.Cu_ppm, All_Soils.Ni_ppm, All_Soils.Co_ppm, All_Soils.Pb_ppm, All_Soils.Zn_ppm
HAVING (((All_Soils.LAT) In (SELECT [LAT] FROM [All_Soils] As Tmp GROUP BY [LAT],[LONG],[Au_ppb],[Ag_ppm],[As_ppm],[Cu_ppm],[Ni_ppm],[Co_ppm],[Pb_ppm],[Zn_ppm] HAVING Count(*)>1  And [LONG] = [All_Soils].[LONG] And [Au_ppb] = [All_Soils].[Au_ppb] And [Ag_ppm] = [All_Soils].[Ag_ppm] And [As_ppm] = [All_Soils].[As_ppm] And [Cu_ppm] = [All_Soils].[Cu_ppm] And [Ni_ppm] = [All_Soils].[Ni_ppm] And [Co_ppm] = [All_Soils].[Co_ppm] And [Pb_ppm] = [All_Soils].[Pb_ppm] And [Zn_ppm] = [All_Soils].[Zn_ppm])))
ORDER BY All_Soils.LAT, All_Soils.LONG, All_Soils.Au_ppb, All_Soils.Ag_ppm, All_Soils.As_ppm, All_Soils.Cu_ppm, All_Soils.Ni_ppm, All_Soils.Co_ppm, All_Soils.Pb_ppm, All_Soils.Zn_ppm;

When I run this query it proccesses nearly to the end of the progress bar then appears to hang ( I have left it for ~1 hour). Is there something wrong with the syntax? I would appreciate some guidance.
Regards,
Peter

[orange]

What is the PK of table All_Soils?
You could make a unique composite index of the fields that uniquely identify your records.
Then build another table with that index defined (no duplicates, no nulls). Then copy the records from your original table to the newly created table - and Access will not allow storing of duplicate records - so you end up with a table of records with no duplicates.

What indexes do you have currently?
Can you tell us in plain English what you are trying to do with the query?

[PeterTaylor]

I have a database with soil samples located at point in space (Lat Long) with analysis for Gold, Silver, Arsenic, Copper, Lead, Zinc and Cobalt. I want to locate records with duplicate entries for all these values. Keep the first one and delete the others. I am very new at this so solutions will need to be pitched at my level.
Regards,
Peter.
PS it is late here so I will attempt your solution first thing in the morning.

[CJ_London]

take a copy of your table before trying this:

DELETE * 
FROM All_Soils 
WHERE ID<>(SELECT First(ID) FROM All_Soils AS T WHERE T.LAT=All_Soils.LAT AND T.LONG=All_Soils.LONG AND T.Au_ppb=All_Soils.Au_ppb AND T.Ag_ppm=All_Soils.Ag_ppm AND As.ppm= All_Soils.As_ppm AND T.Cu_ppm=All_Soils.Cu_ppm AND T.Ni_ppm=All_Soils.Ni_ppm AND T.Co_ppm=All_Soils.Co_ppm AND T.Pb_ppm=All_Soils.Pb_ppm AND T.zn_ppm=All_Soils.Zn_ppm)

Not sure if bit in red is a typo on your part.

Assumption is that ID is a uniqueID for the record

Another option, if the ID is not relevant to relationships in other tables is to run this query to create a new table

SELECT DISTINCT LAT, LONG, Au_ppb, Ag_ppm, As_ppm, Cu_ppm, Ni_ppm, Co_ppm, Pb_ppm, Zn_ppm
INTO NewTable
FROM All_Soils

Then go into newTable and add the ID primary key - note potential typo

Finally, general note about performance - proper indexing is required - perhaps worth indexing all columns for this exercise