Importing one source Into Many related Tables

[Chertsey]

I am about to redesign a database that is not normalized. Currently the users
bring the data into ONE Table from an Excel sheet with 40 plus columns. This
is done at the beginning of each year. Once I’ve normalized the Table into
several Tables, How will I go about getting the proper fields into the proper
tables? I’m hoping I can eventually do this programmatically. However I have
no idea how to import one data source into many related tables. I can give
details about the data if desired. But for now I really want to know if once
my table is split, will I be able to import all the data into the respective
tables? I hope this makes sense.

Regards

 

[Klatuu]

I would import the Excel data into the one table as you are now. Then I
would create an append query for each of the normalized tables to append the
fields from the big table into normalized table.

 

[Chertsey]

Klatuu, thank you for the response. I am attempting to do as you say but am
having difficulty. So let me use a very simple example. Let’s say the ONE big
Table has the following fields. Title, FirstName, LastName, Address, and so
on. So after my import I have records as follows Mr. John Smith, Mrs Jane
Jones, Dr. Jim James etc...
Now once I have normalized my tables I would have a tblTitle and a tblName.
In my tblTitle I have fields TitleID and Title. In my tblName I have fields
NameID as a Primary Key, TitleID as a Foreign Key, and of course FirstName,
LastName etc…
How do I go about getting the right data in the TitleID of the tblName. And
have it properly linked to the tblTitle? I hope that wasn’t too confusing.
Thanks for any help!

 

[Klatuu]

You have your relationships established correctly. What you need to do is in
the append query that you use to populate tblName is to join tblTitle to the
big table on Title. This, of course, means you first need to populate
tblTitle. Then use the TitleID as the source for the TitleID in tblName.

 

[Chertsey]

By George that does it! Obviously my table structure is substantially more
complicated than my simple example, but your directions will do the job!
Thank You So much Kalatuu. Very much appreciated!
Enjoy!

 

[Chertsey]

Klatuu;
Sorry for the spelling error in your Name from the previous post.
Once again Thanks Very much!

 

[Klatuu]

lol
actually, my screen name is a misspelling. It is actually spelled KLAATU