help with importing data

[cdoyle]

Hi,

Another question on this database I'm working on.

I have my database pretty much ready to go, but they would like to import data from another access database into this one.

The problem I'm seeing is, I've been working so hard to normalize my database with the proper relationships etc.

The database I'm importing from, the data there are all stored in huge tables.

so for example in my database I have our main table, that holds all like data. Then my other tables for specfic data that then relate to the main table ID.

The other database has everything in 1 table.

I've created a query and got what I needed from the other database, but not sure how to import it into my main table, and have it so the 'specific' data goes into the right table, and so the Main_ID field points to the correct record in my main table.

I don't think I explained that very well.

Here is how my structure is in the new database

tbl_main
Main_ID
Code_ID
Effective_Date
End_Date
Createdby_ID

tbl_CCE
Main_ID
Codeinfo
etc
etc

now the old database, everything is in 1 table and I'm not sure how to break it down so after I import the table CCE/Main_ID is the same as the Main_ID in my main table.

I'm still not sure if I explained that very well....

 

[The_Doc_Man]

The only way to break something down (technically speaking) involves a thorough and very close reading of the database normalization rules and an understanding of the so-called "normal forms." I see you used the word NORMALIZE so you won't be afraid of what I'm going to tell you.

You have a database (probably a flat-file source) where records include several keys. You have some interdependencies in the multiple keys such that you believe the table is not properly normalized. (OK, I believe you.)

Figure out which fields in the old database depend always and only on a single key. Those things that depend on the same key field probably belong in the same table. Things that depend on TWO keys belong in a separate table. And when you reach that step, you have achieved 1NF (First Normal Form.) Ditto, three keys (God forbid more than three...)

Because the issue is that whatever is the key, its associated fields must be completely and totally dependent on the key value whether single key, double key, or worse.

Where you have values that depend on different keys i.e. some values, key A and B, others key A and C, you have found another table split.

Look at the dependencies to identify the way to normalize. That's one approach if you have some feeling for what normalization means.

 

[cdoyle]

Hi,
Thanks for replying,
Yes the old database seems to be a flat file, it might even be something that's imported from a spreadsheet at one point. Not sure.

The tables, don't even have a unique identifier for each row.

I made a query on it, and got the columns that I need for this new database. There is 1 column that needs to move to it's own table. The rest can go into the main table of my new database.

I just don't know how to import the rest into my new main table, and then this 1 column into the other table, and then have all the data still make sense.

Here is another hitch, the 1 column that needs to move to the new table. It's a 7 digit column (the data is not always 7 digits), but my new table is only a 5 digit.
What I need to have, is the last 2 digits in the old table (if present) move to a new field in the new table.

 

[cdoyle]

Forgot to mention there are maybe 2 tables from the old database that I'm going to have this problem with.


How do I actually go about the importing of the data?

If I just start with the first table in the old database, and then the 2 tables in my new database. I have 3 tables im working with.

I just can't seem to figure out how to import from the 1 large table into my new 'main' table for most of the data that is similar and then that one column into the tblCE? And keep the relationship from the records intact? Main_ID is the PK in my main table, and tblCE has Main_ID as it's foriegn key.

there are about 300k records in this old table, so I want to make sure I do it right and not mess up.

Is this something I can do with a query?