Find duplicates query not working, please help.

[Maggie Barr]

Hello and thank you in advance for helping if you can,
I am using Access 2019 desktop. I have a database I finished, or thought I did, but I need to go in and remove some duplicates for one field. I merged three columns of interest as: Species_Common_Name; Event_ID; BlockName as one field, and I need to remove all duplicates records but keep one associated with that field alone. I have been looking online and tried to run a find duplicates query based off of one of my queries, but I keep getting errors about IIf statements I have in my query and it won't work.
Is there a way to put something in the criteria for the field in design view, like DISTINCT, that will remove duplicates while still keeping one? The entire record is not duplicated on the row level, just within categories I need, and it doesn't matter to me who the observer is, just so long as one record representing the unique value for the merged field remains.
I hope this makes sense. I am trying to figure out how to squeak a query in to fix this issue and change the source for the subsequent query to accommodate the change so I don't have to redo everything.
Any advice would be appreciated.
Thank you for your time,
Maggie

 

[Joe4]

Can you post some data examples so we can get a clearer picture of what you are talking about?

 

[Maggie Barr]

Joe4,
So, a snip-it of the data is below, I have many more fields in the query, but these are the ones I am primarily worried about in regards to the "filter" for duplicates.
As you can see below, the merged columns shows a duplicate (row 1 & 2 for instance) but the observer ID is not a duplicate. I need to make the query filter out all but one of the duplicates from the merged column I don't care who the observer is. Because of so many IIf statements in the other fields, it is not even letting me produce a find duplicates query based on this query. I was hoping to be able to put something perhaps in the criteria for the merged column in design view that would select out distinct records, but not remove all the records that are duplicates.
Any help is appreciated, thank you,
Maggie

[TABLE="width: 518"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]New_Common_Name; New_Event_ID; BlockCode[/TD]
[TD]OBSERVER ID[/TD]
[/TR]
[TR]
[TD]American Crow; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]American Crow; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]American Goldfinch; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]American Goldfinch; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]American Tree Sparrow; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]American Tree Sparrow; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Black-capped Chickadee; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]Black-capped Chickadee; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Cooper's Hawk; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]Cooper's Hawk; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Dark-eyed Junco; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Dark-eyed Junco; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]Downy Woodpecker; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Downy Woodpecker; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]Great Blue Heron; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Great Blue Heron; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]Herring Gull; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
[TR]
[TD]Herring Gull; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]House Sparrow; G4009716; Portland East_CW[/TD]
[TD]obsr99498[/TD]
[/TR]
[TR]
[TD]House Sparrow; G4009716; Portland East_CW[/TD]
[TD]obsr188775[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You don't actually need to merge the three fields into one to do this.
Simply add those 4 fields to a new query, and then from the Query Builder, click on the Totals (Aggregate) Query button (looks like a Sigma).
This will add a "Totals" row to the query, with the phrase "Group By" under each field.
Simply go to the one under the "Observer ID" field, and change "Group By" to "Max" or "Min".
This will "group/collapse" all records that have the same values in the "Group By" fields, and show the "Max" or "Min" value of the Observer ID field.
That should show you what you want.

 

[Maggie Barr]

Joe4,
I will give that a try tomorrow, but I need it done on the whole dataset in the query, can I leave all the fields there, and do it just using the fields you spoke of? That way I can then use that query as the base for subsequent queries.
Thanks,
Maggie

 

[Joe4]

Yes, use this query as the basis of subsequent queries.
You can join this query to your main data source, joining on all 4 fields. Then you can return any fields from your main data source that you want.

 

[Maggie Barr]

Joe4,
You are an angel, thank you!

I couldn't wait until tomorrow, and my son is working on homework anyway. I did what you described, but I left my merged column as is for simplicity. It worked as you said and reduced the number of records from 208,981 to 172,930. I had never used multiple fields as a join for tables/queries before.

I used the group query as the basis for another query to bring all the data back in (or at least the records I wanted from the grouping). I joined that back to the full dataset using both the merged column and the observer ID, but I got more records than I had with just the grouped query (173,018). I checked, as I was sure it was my dataset, and it was. Sometimes an observer can list a species and a form of the species which become separate records, but all our analysis is at the genus species level, so it makes it look like the species was entered twice during the same sampling event at the same location. I think that we can live with an 88-record discrepancy, and it will only affect the effort calculations minimally.

So, again, thank you so much for your help, you are very kind.
Best wishes,
Maggie

 

[Joe4]

You are welcome, I am glad it worked out.

Yes, you can join two object on one or many fields. Just go into the Query Builder and keep dragging connecting lines from the fields in one table to fields in the other table.
If you have a lot of records, you may find that runs more quickly/efficiently than joining on calculated fields.