Normalisation Problems

[Chris Morris]

I have an Access db with two tables: tblProjects and tblGrants - each Project can have many Grants and each grant includes information on where the money is coming from (a fund) and how much it is and when it is scheduled to be paid.

There is now a requirement to migrate this information to a new Project/Grant maintainance database, however the new db has three levels: Each Project has many Grants, each Grant can have many Transactions (so formerly the Grant and Transaction information were in the same table).

I can migrate the Projects no problem and I can "Group By" the old Grants table, so I only get grants where the Project ID and Fund ID match, however the problem occurs when I want to re-migrate the Grants table into the Transactions table for the Transaction level information. How can I ensure the transactions "know" which grant to attach to?

 

[KenHigg]

How many records are there in these tables?

 

[Chris Morris]

The projects table has around 1200 records and there are around 3200 grants, however the final migrated tables would include the same 1200 projects, about 1300 grants (so most projects only have one grant) and then the same 3200 transactions.

If it was a one-off I would use the Table Analyzer wizard, but whilst the front end is developed for the new database I need to repeat the import process consistantly (and the data is also being used daily).

 

[KenHigg]

I think I would consider isolating the one to one's and the manually key in the one hundred. By the time you tinker with trying to automate it you could have it done. Is this an option you would consider?

 

[Chris Morris]

Thanks Ken, I'll give that a go.