Access-Duplicate issue in tables looking for suggestions about automation

[Jenaccess]

Hi,

I'm working with Access 2010, though I'm sure advice from Access 2007 would be applicable.

Basically, I have three tables:

tblStudent
tblService
tblStudentService (junction table between tblStudent and tblStudentService)

My tblService has almost 3,000 records. Most of these records, unfortunately are duplicates. They are the same service (name, time, date, category). However they have different ServiceID's, which is the primary key of tblService, so Access thinks they're different.

My tblStudentService is made up of StudentServiceID (pk of that table), StudentID (fk) and ServiceID (fk). This junction table now has the wrong information because of the duplicate ServiceID's. Instead of having 100 students enrolled in the same ServiceID (which should be the case), those same 100 students are enrolled in 100 different ServiceID's (though it is actually the same service in every other field).

What I have to do now is look at all my services in tblService and figure out which ones are the same in every field but the ServiceID. Those will be the duplicates. Then I have to take the first ServiceID that is not a duplicate and plug it into the ServiceID (fk) field in my tblStudentService. Then I have to delete the duplicate service from tblService.

I have between 3,000 and 4,000 of these to correct, so doing all of them manually would take quite a bit of time. Would anyone be able to suggest a function or some code that would automate at least some of the process? If anyone has any advice, I'd be very grateful. Thank you!

 

[AccessBlaster]

Have you tried running the "Find Duplicates Query Wizard"? While this will not solve your problem, it will group them together.

 

[Jeanette Cunningham]

Be sure to keep a backup copy of the tables before you start working on them.
Perhaps try like this: untested, but it gives the idea.

Make a query that joins StudentService table to the service table, include only the studentId and the Service description.

In design view change the query to show only unique values - on the property tab of the query.

Use this query to populate a new table – tableA - that will have fields for studentID (Primary key) and Service description (text).
Create a new table – tableB -for service without any data.
Use a query to get the service descriptions out of tableA – one field only – there should be only copy of each service in this query.
Append the service descriptions into the tableB, the new primary key values will be created in tableB and you now have a new service table with no duplicates.
Now you can add a number field, long integer to tableA for the serviceId.
Create a query joining tableA to tableB and join them on the service description field. Use this query to update the ServiceId field in tableB.
Now you have removed the duplicates.

 

[Jenaccess]

I'm sorry it took me so long to reply. It actually took me a few days to process my task and be mentally ready to tackle it (if that makes any sense ).

Thank you AccessBlaster I did start with the duplicate query wizard, and thank you Jeanette for helping me understand where to begin. I did a lot of the manipulation in Excel, but I followed your basic guidelines and was able to get the job done in about an hour. Thank you for helping me save lots of time!