Duplicate records

[rohanmalhotra83]

Hi

I am a beginner in access. My problem is that i have created a database, which has a list of customers with their unique ID's. Also, have many duplicate records. Table also has a Status field which can be Email sent, Chaser Sent and Amount Received. I need to find out and delete the duplicate records from the table keeping the last updated status. Could someone help me with it? Thanks in advance.

 

[JamesMcS]

Hi, welcome to the forum - you can create a query to do this I expect but what is it you're trying to achieve? Is it the latest statues for each customer ID?

 

[rohanmalhotra83]

Hi James

Yes i am looking to keep the latest status of all duplicate records and delete rest of them.

 

[JamesMcS]

OK so we need to identify which is the most recent record - have you got a date field we can use?

 

[rohanmalhotra83]

Yes i have a date field and also an ID field as date can be same

 

[JamesMcS]

OK so create a query with customer ID, status and date. Click on the sigma button to bring up the totals row in query design (the big E in the toolbar), group by everything except the date - choose "Max" for that. That will bring back the latest record for each customer.

 

[vbaInet]

Just wondering how many tables you have? Sounds like you only have one.

 

[rohanmalhotra83]

Yes i have only one table. However, will this give me dupliucate records?

 

[JamesMcS]

Nah it should be OK. Have you tried the query suggestion I put in above?

 

[rohanmalhotra83]

Yes i did, but that is giving me the entire list

 

[vbaInet]

With regards having one table for customers with duplicate transactions, normalisation comes to mind.

 

[JamesMcS]

If you've grouped correctly you shouldn't see duplicate IDs or statuses.

 

[vbaInet]

If you've grouped correctly you shouldn't see duplicate IDs or statuses.

That's not my point. My point was normalising, nothing to do with the proposed solution.

 

[JamesMcS]

Apologies VBA, I should have quoted the post I was replying to (#10)

 

[vbaInet]

Ah, got you James.

 

[rohanmalhotra83]

I have grouped them correctly, Date field has Max and rest of the fields Group By