CODEWITHSUNDEEP
ms-accessms-access-2010
DHPA
DHPA

Reputation: 55

re-establish relationship link

So I had an issue/problem that is detailed here (Sorting error in one field of Report), which leads to my current problem.

I have two tables (Owners and Boats) that were once joined in a relationship via the fields Boat_Owner – (number field on Boat Table) and Owner_ID - (Primary Key autonumber on Owner Table). Due to a problem the data type in the number field on Boat Table has been changed from number to short text.

So now the relationship is no longer working and as a result I’m getting the ‘data type mismatch’ error.

Both tables are intact with table Boat having 355 unique entries and table Owner having 295 unique entries, some owners own more than one boat. The autonumber ID’s are still intact in both tables so for example I know that Boat 401 finds its ownership with Owner 33. Unfortunately I cannot figure out how to re-establish the table relationships due to the aforementioned field data type issue.

I’m thinking I can run an append query to re-establish the owner_ID field into the Boat table as a number field.

From this point I’m going to manually enter all of the owner_ID. Far from desirable as there are 295 of them. I have been using excel to match the two tables on one spreadsheet but I’m open to suggestions if someone has a better procedure.

If it does work my thought is I could then re-establish the relationship between the two tables.

I’m not sure if I am going about this the best way, or even if this is the correct way to go about this.

If anyone has input I’m open to suggestions. I’ll post my progress if there is some.

Upvotes: 0

Views: 43

Answers (1)

Andre
Andre

Reputation: 27644

Right now the column contains the names in text form.

Then you can do the following:

Create a new column in Boats: Owner_ID Number, Long integer.

Then fill this column with an UPDATE query from a join via the owner names:

UPDATE Boats INNER JOIN Owners
  ON Boats.Boat_Owner = Owners.Owner_Name
SET Boats.Owner_ID = Owners.Owner_ID

If there are duplicate names (John Smith), there will be wrong assignments and you'll have to correct these manually.

When all Owner_ID's are assigned correctly, you can create a new 1:n relationship, now from Owners.Owner_ID to Boats.Owner_ID.

Afterwards you should delete the Boats.Boat_Owner column, it is now useless.

Upvotes: 0

Related Questions