Remove duplicate records again!

[BarryMK]

I am trying to clean up data from a reporting application which is totally pants. The data were exported from Excel into A97. I have attached a shot of the (simplified) table grid.

What I need to arrive at is one record per CaseFullRef as opposed to the multiple instances I start with. Setting unique records/values in the properties doesn't work. Any help appreciated.

Barry

 

[Brianwarnock]

What do you want to do with the different info in the code field?

brian

 

[BarryMK]

What do you want to do with the different info in the code field?

brian

Good point Brian, as it happens it's not relevant and I should have deleted that column from the example.

 

[Brianwarnock]

Can you not create a new table with unique on the CaseFullRef field using an append query to the empty table.?

Brian

 

[BarryMK]

Can you not create a new table with unique on the CaseFullRef field using an append query to the empty table.?

Brian

Sorry Brian you've lost me I can't find any way to make the CaseFullRef field unique, either in the table or a query.

 

[Brianwarnock]

Make it Indexed(No Duplicates) in the new table design.

Brian

 

[John_W]

Use a Totals query, grouping on CaseFullRef and the other relevant fields.

 

[BarryMK]

Hi Just back from Lunch

Brian & John many thanks. I tried John's way and it works. Why does the answer always seem so simple once you get it??

 

[Brianwarnock]

I don't want to be picky but I assumed that you wanted to end up with a table without dups and presumably prevent their creation in the future.

Brian

 

[BarryMK]

I don't want to be picky but I assumed that you wanted to end up with a table without dups and presumably prevent their creation in the future.

Brian

Hi Brian No problem. The report is run once a month and will always have duplicated records. The original app cannot run a query that returns records where specific actions (the code field is the action) have not occurred. So I have to export all records with a specific enquiry type which will show records with and without the missing?? codes. I then delete records with the two codes from the access tables and run the dedupe query as what we need is one example of each address where those codes don't exist so we can plot the address on GIS. Sorry if I didn't explain clearly what I was after, but thanks as ever for your input.
Cheers