Query Hanging

P

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Dear All,
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
this should be easier to read

Code: 
[FONT=Courier New][SIZE=2][COLOR=blue]SELECT[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=#FF0080][B]Max[/B][/COLOR][COLOR=maroon]([/COLOR][COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]id[/COLOR][COLOR=maroon])[/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=maroon]MaxOfID[/COLOR] 
[COLOR=blue]FROM[/COLOR]   [COLOR=maroon]all_soils[/COLOR] 
[COLOR=blue]GROUP[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR] 
[COLOR=blue]HAVING[/COLOR] [COLOR=maroon]([/COLOR][COLOR=maroon]([/COLOR] [COLOR=maroon]([/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR] [COLOR=maroon])[/COLOR] [COLOR=blue]IN[/COLOR] [COLOR=maroon]([/COLOR][COLOR=blue]SELECT[/COLOR] [COLOR=maroon][lat][/COLOR] 
                                [COLOR=blue]FROM[/COLOR]   [COLOR=maroon][all_soils][/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=maroon]Tmp[/COLOR] 
                                [COLOR=blue]GROUP[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon][lat][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][long][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][au_ppb][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][ag_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][as_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][cu_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][ni_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][co_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][pb_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][zn_ppm][/COLOR] 
                                [COLOR=blue]HAVING[/COLOR] [COLOR=#FF0080][B]Count[/B][/COLOR][COLOR=maroon]([/COLOR][COLOR=silver]*[/COLOR][COLOR=maroon])[/COLOR] [COLOR=silver]>[/COLOR] [COLOR=black]1[/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][long][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][long][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][au_ppb][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][au_ppb][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][ag_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][ag_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][as_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][as_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][cu_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][cu_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][ni_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][ni_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][co_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][co_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][pb_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][pb_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][zn_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][zn_ppm][/COLOR][COLOR=maroon])[/COLOR] [COLOR=maroon])[/COLOR][COLOR=maroon])[/COLOR] 
[COLOR=blue]ORDER[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR][COLOR=silver];[/COLOR]  [/SIZE][/FONT]
 
Upvote 0
mole999 said:
this should be easier to read

Code: 
[FONT=Courier New][SIZE=2][COLOR=blue]SELECT[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=#FF0080][B]Max[/B][/COLOR][COLOR=maroon]([/COLOR][COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]id[/COLOR][COLOR=maroon])[/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=maroon]MaxOfID[/COLOR] 
[COLOR=blue]FROM[/COLOR]   [COLOR=maroon]all_soils[/COLOR] 
[COLOR=blue]GROUP[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR] 
[COLOR=blue]HAVING[/COLOR] [COLOR=maroon]([/COLOR][COLOR=maroon]([/COLOR] [COLOR=maroon]([/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR] [COLOR=maroon])[/COLOR] [COLOR=blue]IN[/COLOR] [COLOR=maroon]([/COLOR][COLOR=blue]SELECT[/COLOR] [COLOR=maroon][lat][/COLOR] 
                                [COLOR=blue]FROM[/COLOR]   [COLOR=maroon][all_soils][/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=maroon]Tmp[/COLOR] 
                                [COLOR=blue]GROUP[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon][lat][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][long][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][au_ppb][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][ag_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][as_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][cu_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][ni_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][co_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][pb_ppm][/COLOR][COLOR=silver],[/COLOR] 
                                          [COLOR=maroon][zn_ppm][/COLOR] 
                                [COLOR=blue]HAVING[/COLOR] [COLOR=#FF0080][B]Count[/B][/COLOR][COLOR=maroon]([/COLOR][COLOR=silver]*[/COLOR][COLOR=maroon])[/COLOR] [COLOR=silver]>[/COLOR] [COLOR=black]1[/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][long][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][long][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][au_ppb][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][au_ppb][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][ag_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][ag_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][as_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][as_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][cu_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][cu_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][ni_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][ni_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][co_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][co_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][pb_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][pb_ppm][/COLOR] 
                                       [COLOR=blue]AND[/COLOR] [COLOR=maroon][zn_ppm][/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon][all_soils][/COLOR][COLOR=silver].[/COLOR][COLOR=maroon][zn_ppm][/COLOR][COLOR=maroon])[/COLOR] [COLOR=maroon])[/COLOR][COLOR=maroon])[/COLOR] 
[COLOR=blue]ORDER[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lat[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]long[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]au_ppb[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ag_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]as_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]cu_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ni_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]co_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]pb_ppm[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]all_soils[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]zn_ppm[/COLOR][COLOR=silver];[/COLOR]  [/SIZE][/FONT]
Click to expand...

Thanks Mole999,
Do I cut and paste this to a query? I will try tomorrow as it is late here
Regards,
Peter
 
Upvote 0
its your query, i haven't changed a jot, just formatted too read easier. i would junk the order by ( to the end) as that is already implicit by values being pulled.

i don't know much about group by as its a dark art and my brain hasn't come to terms with it
 
Upvote 0
mole999 said:
its your query, i haven't changed a jot, just formatted too read easier. i would junk the order by ( to the end) as that is already implicit by values being pulled.

i don't know much about group by as its a dark art and my brain hasn't come to terms with it
Click to expand...

Dear Mole999,
Thanks for the help I got it done but the process was protracted:
  1. Run the Find Duplicates Query that is in the wizard on All_soils making a table of the query result ("JusttheDups").
  2. Change the above Query to a delete query and delete all Duplicates from All_Soils.
  3. Run the Find Duplicates Query that is in the wizard as a delete query on "JusttheDups" making a table of the query results ("UniqueDups"). This used the same query format as you edited in previous post; it took ~20 minutes to run on the "JusttheDups" table which was ~35,000 records so it is any wonder it was not completed overnight when the query was run on All_Soils table with over a million records.
  4. Append the uniqueDups table to the All_Soil table. Done!
It would appear that working with comparison on this many fields(10) is at the limit of this approach. Are you aware of any other way I could go that would be more efficient?
Regards,
Peter
 
Upvote 0
just make sure that you trap duplicates (unique key) in access before they are created (I used to just add all the fields together in a record and use that to trap duplicates), or be able to set a marker that runs off the update when a duplicate is found, so they can be excluded I have a feeling ten to the power of ten is what was occurring
 
Upvote 0
Always consider looking at how the table is indexed to improve performance, especially for large tables.

You might also be able to "remove duplicates" by setting unique keys on a new table, and just importing the data into the new table (if the unique indexes are set as needed, duplicates will automatically be discarded when they break the rules). That's just an idea - not sure if it would be faster or not but I would think so.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
DCount query
Replies
3
Views
658
welshgasman
welshgasman
G
  • Question
Access 2013 Duplicate Finder Subquery Issue
Replies
8
Views
456
xenou
xenou
M
  • Question
Create a Single Consolidated Table from Multiple Linked XLS Tables
2
Replies
19
Views
2K
Manerlao
M
M
  • Solved
Using VBA to change a single parameter in a long SQL statement
Replies
7
Views
1K
Micron
M
P
  • Question
putting a snippet of code from one to another somehow
Replies
5
Views
270
Patriot2879
P

Forum statistics

Threads
1,221,869
Messages
6,162,530
Members
451,773
Latest member
ssmith04

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top