I have an access database that I converted to FM12. The db is a flat file that contains about 1500 old classic cars and their ownership history. Each record consists of the car VIN (which is unique) and a list of owners from 1 to n, over the years, with each owner name residing in a different field, e.g. owner 1, owner 2, ....owner n.

 

I have created a relational db with three tables: cars, owners, and a join table called transactions. My question is how do I first establish the relationship in separate tables between each owner and the unique VIN, and then how do I "import" them such that each owner field will be placed in a field simply named "Owner".  i suspect this will require a loop for each record, but I am not yet able to figure that out until I can solve the first problem.

 

Thanks for any help.

 

wally buch

You actually don't need the join table Transactions in this case - due to the limitations of your source data, you can't tell if one owner has owned multiple cars, only that a car has had multiple owners, so it's a simple

 

Cars -< Owners

 

structure (You could surmise that owners with the same name are the same person, but you can't guarantee that...)

 

Import the Cars with the VIN as the key

 

Then do multiple imports  - one for each Owner n - to the Owners table and relate on VIN

 

I'd then create a proper key field, and populate the foreign key field in the Owners table and re-create the relationship on that, but it's not totally required. VIN is supposed to be unique (although across countries of manufacture, this may not be true)

Unfortunately one owner can own many cars and even the same car more than once, so I need the intersection table. And there are perhaps 6000 transactions over the past 50 years so importing them one by one and manually linking them is a bit daunting. Is there a way to automate this?