Duplicate Rows With Related Records (Update Related Records and Remove Duplicates)

[bigot]

Hi,

I found one other thread with one response regarding this question, and the response isn't very clear (in fact, I'm not even 100% sure the original poster is trying to do what I'm trying to do). Hope I can get some help here.

Consider the following: I have two tables, one is of departments, and one is of people (with a FK denoting what department this person is in). Now consider the fact that there are duplicates in the departments table, and I would like to remove these duplicates. However, the duplicates have related records (in the people table). So, before removing the duplicates, I must update the FKs in the table of people (this is the step I'm having trouble with).

Confusing? Here's an example:




As you can see, the "Sales" department is there twice. And both have a related record. What I want to do is:

1. Update all DepartmentIDs (in tblPeople) to not point to duplicate records. In this example, that would be PersonID 2; Joe. His DepartmentID should update to "1" (as both "1" and "2" are "Sales").
2. Delete the duplicates in tblDepartments (in this case, DepartmentID 2, "Sales").

The second step is no problem, it is only the first I am struggling with.

Also, the example posted here is just an example, the data I actually need to do this for is significantly more complex and there are many more records!


In the attached database:
qry1: Simple query to find all duplicates (just used the query wizard)
qry2: Just the first row of each duplicated departments (duplicates that shouldn't be deleted). In the example above, this would be the "2", "Sales" row in the tblDepartments table.
qry3: Basically all qry1 rows that don't appear in qry2
qry4: All qry3 values, and their respective qry2 value. This is what each of the (soon to be deleted) duplicate values' related records' DepartmentID should be updated to... There's no simpler way to phrase that, so using the example above, qry4 would return "2","1". This indicates that all people with a DepartmentID of "2" should be changed to "1" (so we can subsequently erase the department with the ID of 2.

This is as far as I have gotten. My next step is: Update all FKs in tblPeople based on qry4 (You can't set an update query's criteria to pull from another query, nor can you use the second query for the update value... or maybe you can, but I don't know about it).

Any help would be appreciated; if you don't know how to solve the problem but have an idea of what I should try or a direction to go in, please don't hesitate to post!

Thanks.

[recyan]

not sure whether this is what you are looking for / have you tried this:
pseudo logic sql query :

UPDATE tblPeople SET DepartmentID = 1 WHERE DepartmentID = 2;

[NTC]

realigning keys is tricky. recyan post is correct. as you post yourself as a novice I will break it down: first make a simple select query for all Names with department 2. call that 'ToBeChanged' query. Then make a new query with people table and your ToBeChanged query which joins on the person's ID key field. turn this query into an Update query, drag down the persons table Dept ID field and manually enter 1 as the UpdateTo value. This will change all 2s to 1s. then repeat for each dept.

[bigot]

not sure whether this is what you are looking for / have you tried this:
pseudo logic sql query :

UPDATE tblPeople SET DepartmentID = 1 WHERE DepartmentID = 2;

Yeah, that's exactly what I'm looking to do, except as I mentioned in my original post;

the data I actually need to do this for is significantly more complex and there are many more records!

So, there would be over 1000 update queries in my actual data, and doing it manually is bad way to go.



What I ended up doing (long process but works well):
I took the query that shows all duplicates (qry1DupsDepts in the database attached to original post), and a query that shows only the first duplicate (qry2DupsDeptsFirst). I created a new query that joined them on all columns except the ID (my data had more than 2 columns, unlike the example attached).

The new query has two columns; first column shows all of the duplicate IDs, second column shows the ID it should be changed to. I just pasted these into Excel, and wrote a simple formula to create an update query (including "DoCmd.RunSQL", so I can paste it right into my VBA module).

If you do this method, be sure to disable update warnings ("DoCmd.SetWarnings False") before all the update queries, and re-enable them after the queries (same line but "True" instead of "False"). I made the mistake of not doing that once (thank goodness for Ctrl+Break).

---

I was aware of the solution I posted above, but I was hoping veteran Access users would have a less ghetto-rigged, more elegant solution. I imagine this is a common problem, especially when you're working with a database someone else designed (without proper data validation).

Thank you for your responses.