Tables and Form Issue (1 Viewer)

I created a database based on data exported from excel. I followed through with the wizard which automatically created the relationship and look up tables for me.

There is now a problem I am facing on two of the generated tables. I am having 2 identical departments with two different ID. The same is true for the location table that it is generating 3 identical location with 3 different ID. I am having difficulty merging this. I have even tried to correct this on the main table but it has not effected on the respective department and location table. Any assistance with a solution please?

Your table probably has an autonumber ID field.
Make a backup copy with the suffix BKP
Then create a new copy with the correct table name but do not add the data YET
Then change it so the table has DepartmentID as its primary key field.

Now use an APPEND query to add records from the BKP table
You will only get one record for each dept.
Check the result. If OK, delete the BKP table

I have never used the wizard to do this. I always do it myself so I'm not sure what problem caused this. I'm going to guess that some department values are similar but not actually identical. Perhaps there are extra spaces in the text or hidden characters. If there are only a few instances of incorrect data, I would make a cross reference table manually. The table will have two columns. OriginalDeptID, and MergeToDeptID. You would then run an update query that joins the DeptID FK field to OriginalDeptID and updates that field to MergeToDeptID. At the end of the query, you will have eliminated all references to OriginalDeptID. You can then remove those rows from the Department table. If RI is enforced, you will get an error if all the references were not removed but if all references were removed, the query will just delete the no longer needed rows.