Importing data from old Access database to new database

Actually this isn't difficult given your situation. Since your data is currently in only one table, you link that table to your new database, then run a series of Append queries to move the data into the new tables. Just make sure to include the old PK into the new FKs.

Thank you for the quick response!

 

Sorry I am pretty new to Access/databases.  To be more specific on my scenario:

The old database (1 table) has 90 columns in it. Because there are no relationships, it was being used essentially the same way an excel spreadsheet gets used. Its just one big datasheet.

I found some columns that had repeating values in the fields and made separate tables into them to clean up the data for that I can also easily create combo boxes on forms that refer to the list in the related tables.  So all of my relationships in the new database are one-to-many (the one being the related ("list")  tables and the many being the "Main" table.  So I still have one "Main" table which holds many of the columns that the old table had, but I also have 12 FK's in it that relate to the tables I created.  In other words, I still have one fairly large table that also has FK's now.

When I am making my queries, I am confused on what you mean by "just make sure to include the old PK into the new FKs". How am I making my query so that the records don’t lose their related data? Since I still have one main table that just relates to 12 other tables (1:M), do I make an append query for the main table? If so, I get that I am matching the old fields to the new fields, but how am I connecting the FK number in the main table when the value that I am matching it up to is a text value from the original table?

Thank you!

Ok, lets use an example. Lets say this is a contacts database. You have 2 tables; contacts and actions. Contacts is your list of people, Actions is the engagements you have with those people. So you run an Append query from your old table to Contacts:

INSERT INTO Contacts (ContactID, Firstname, Lastname, etc.)
SELECT ContactID, Firstname, Lastname, etc
FROM oldtable

Now you need to append the actions. so that query would look like this:

INSERT INTO Actions (ContactID, ActionDate, ContactBy, etc.)
SELECT ContactID, ActionDate, ContactBy, etc
FROM oldtable

The other scenario is if you are breaking out the data into lookup tables and your Foreign Keys are in the main table. So in that case, lstill using the Contacts example, you have your contacts table and a category table to indicate the type of contact. So now your Append query would look like this:

INSERT INTO Contacts (ContactID, Firstname, Lastname, CategoryID, etc.)
SELECT Contacts.ContactID, Contacts.Firstname, Contacts.Lastname, Category.CategoryID, etc.
FROM oldtable, Category INNER JOIN on oldtable.Category = Category.Category

You would have to create the Category table first:
INSERT INTO Category (Category) SELECT DISTINCT Category FROM oldtable

This is assuming you are using Autonumber PKs which will be generated during the Append.

Because I am not confident in SQL and what your explaining at this point I tried a different approach which sort of worked..

The only solution that I thought of is to change the PK on all the list tables to the actual text since this is essentially a list and there wont be duplicates this would work.  I would then have to change the FK field in the main table from number to text and re-relate the tables.  The biggest problem that I see with doing this is that I would have to enter all the keys in the related "List" tables with all of the possible keys before performing this append query.  I tried to do this already and the append query added only the records that had the keys already in the list table. I was hoping it would add the values that I did not enter yet that are in the old database.

 

If I am going this route it would be easier if I could find a way to find make a query that appends the list of possible values in each table also without adding duplicates. I don’t know if this is possible. I use the QBE instead of SQL because I am not proficient in SQL yet… This is probably also a considerable amount of more work than what your approach is..

 

Thank you for your response.

First, it doesn't matter whether you use the QBE or enter the SQL directly. I generally use the QBE even though I'm capable of writing SQL statements from scratch. But using queries is the best way to handle this. But the process should be to FIRST populate your lookup tables. This is done by selecting the Text field from your old table. Setting the properties to UNIQUE values. Test by viewing the query results. Then turn it into an Append query and populate your lookup tables.

Once your lookup tables are populated. You can then populate your main table using the second scenario I described.

I am still confused on this:

When I make my large append query that is moving all the records from the old database to the new database, how am I tying the TEXT field of [OLFACTIVE FAMILY] from the old database to the FK field in the new database which in an AutoNumber datatype? This move does not make sense to me and I don't get how am I keeping this relationship.

Should I make all of the list tables primary key a text value and get rid of the AutoNumber field? Meaning in all these tables I just have a field which is the PK and holds the unique text value that I am listing? This would allow me to tie the [OLFACTIVE FAMILY] text field from the old database to the [OlfFamID_FK] field in the main table of the new database (assuming I change this to a text field as well instead of Long Integer..

Thanks

Hi,

I have followed the examples by adding the unique values in my list tables.  One thing that I noticed is that for some reason when I click the datasheet view in my append query to view the append, the total number of records is short.  I have some blank values in the fields in my main table that has foreign keys that I am trying to append and I did the math to figure out that the query is appending only the FK's which hold values but if it is blank, it won't append it to my main table. When I ran the append queries for my list tables it did add a primary key which has a value of blank in the list field so I am unsure of why this is happening. Shouldn't the primary key match up to the foreign key even if the field for the listing of values is blank?

The only fix around this that I could do if there is no solution would be to add a value like "N/A" to the blank fields so that then there would be a key with a value instead of a key with a blank...

Thank you

Yes I am trying to use your demo to help me understand the concept but I must not be grasping something about this process.  I have linked my old table to my new database. So the queries to append unique values to my list tables is easy. They have a primary key and one field that holds the list of different values (each value has its on primary key in other words).  This fills my list tables with only the unique values that will be the old table that I am bringing to the new table.  The area that I run into problems is creating my query that fills the main table (the table that holds all the foreign keys). 

Here is just the beginning of me building this query.  The sheet1 table is the old table where people basically used Access using one table...I am trying to make it better by separating some of the values that get repeating into separate tables. tblApplication is an example of this.  When I turn this query into an append query, I tell it to append to a table named tblFragrance which is basically my new version of sheet1.  The difference, like I said, is that it holds many foreign keys in replacement of just having a "Application" field it now has a field "AppID_FK" which I connected to the AppID in the relationship view as a one to many relationship (there can only be 1 application per fragrance). BTW - I added the autonumber criteria because I am only trying to append the values equal to or greater than this number.

I am using the application to start because I know that none of the records have NULL applications (a blank value in the APPLICATIONS field from the old sheet1 table). When I view this in datasheet view I see that the FK is relating to the correct value. But when I go to run this query, it says it cannot add any of the records due to key violations....

Any help in the right direction would be greatly appreciated. There seems to be little info online that shows how to do this what I would think would be a common action.

Jacob