Link to home
Start Free TrialLog in
Avatar of DJ P
DJ PFlag for United States of America

asked on

MS Access Duplicate Data Assistance

Hello experts. I am not an access expert so I'm hoping to get some help here. I have an ms access form on the front end connected to a sql database. The form has several tabs. On the payroll tab there appears to be what I think is a subform for wage history. The form is continuous. The issue I am having is on certain records for the wage history I am get duplicate data. I provided an attachment for reference. As stated I am not expert in access at all but when I look at the database tables and records there is no duplicate data in the tables what so ever. This would make sense to me as it appears the employee number is the unique primary key.

So my question is how do I get rid of this duplicate data or how do I trouble shoot to see where it is coming from?
screen-shot.docx
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

I suspect that the RecordSource for the Wage History subform includes records from Employees or other tables. What is the value in the RecordSource property  (Property Sheet - ask if you don't know how to turn this on) for the form inside the  Wage History subform control?
Avatar of DJ P

ASKER

tbl_Wage History
If you open tbl_Wage_History, you should see the duplicates there also.  I would look at the process that creates history since that is what is creating the duplicates.  Once you fix the bug, then you can delete the duplicates.
Avatar of DJ P

ASKER

There are no duplicates in the tbl_wage_history. I have traced that back to the additional tables and there is no duplicate data that I can see. That's what's throwing me.
Avatar of DJ P

ASKER

I stand corrected. I do see some dupes in that table. How do I trouble shoot from there? What is the best course of action?
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Crystal's advice will work but you'll need to warn your users that they will be getting new error messages about the duplicates if you don't take the time to find the code that is causing the duplicates and fix it.  Pay me now, pay me later.  You can't really avoid fixing problems.
Avatar of DJ P

ASKER

Ok so as a test I ran some sql to delete one of the duplicate records and it says it updated one record. However when I do a select * on the table the record does not delete.
There is rarely a way to run a delete query to delete duplicates because there usually isn't a way to differentiate  which of the n records should stay and which should go.  The easiest way to get rid of duplicates is to rebuild the table.

Copy the table schema but not the data.
Add a new unique index that will prevent the duplicates from being readded.
Create an append query that selects all the data from the old table.  You might be able to sort the data such that the record you want to keep from a set of duplicates will be returned first.
Run the append query.  You'll get a warning message at the end telling you that n records were not added due to key violations.

If you need to keep the autonumber primary key, you can but you would then need to deal with child records that are related to duplicates that might get deleted first.