DJ P
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
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
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?
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.