Questions

Getting data from an old Access table into a new Access database?

Tags:
+
0 Votes
Locked

Getting data from an old Access table into a new Access database?

chrisd
I've done some searches, but it's possible that I'm not searching on the right terms, or perhaps that I just don't understand the answers, but I've not found the information I need.

I'm relatively new to Access, and I've been pleasantly surprised with how robust of a solution it's turning out to be. As for my experience, I took database classes to get my Bachelor's so I understand basics of database design.

I came to this company a couple years ago and was handed a database that tracks plan review projects.

1 Project can have many reviews
1 review can have many plan checkers

In the old database, the Project and Review information was stored in one table. (Plan Checker information is something I'm adding to the database in the new iteration.) Something like this.

ProjectInfo
-------------------
id_ProjectInfo
PlanCheckNum
ProjectName
ProjectDesc
ContactName
ContactNum
Comments
ItemsRecieved
DateIn1
DateOut1
SentVia1
SentTo1
DateIn2
DateOut2
SentVia2
SentTo2
DateIn3
DateOut3
SentVia3
SentTo3

Etc...

I've dealt with the horrid nature of writing queries and reports on this database, and decided to do a complete redesign. I've created a new database structure with three tables. One for Projects, one for Reviews, and one for PlanCheckers. I've created relationships, written reports queries, and created nice forms using bogus data, etc. I'm REALLY pleased with the new design and how it turned out. My question is this:

How do I get the data from the old database to the new one while keeping the relationships intact. I mean, a project could have up to 4 reviews in the old database, but they're all in one record on one table. Is there an easier way to do this than exporting the single table to Excel and manually fiddling with the data before importing?
  • +
    0 Votes
    RationalGuy

    "Is there an easier way to do this than exporting the single table to Excel and manually fiddling with the data before importing?"

    Other ways may not necessarily be *easier* ways. You can build Access queries to "pre-fiddle" with your data. You can build some VBA code that connects to the old dB, queries the tables and INSERT queries the data into the new dB.

    Considering this is a one-off data move, Excel might be the most straightforward approach. It's very flexible, and you can easily move a lot of data around very quickly in a variety of manners,

    +
    0 Votes
    chrisd

    I was afraid of that answer. I couldn't think of another simple way to preserve the relationships between the three tables when I'm moving all the records from one table to multiple tables, etc. Not that this is going to be "simple", but it's easier than anything else I could think of.

    The annoying thing is that I've got to cut over this database while no one is in it, because they're constantly entering data into it. It's going to have to be a weekend job, I think.

    Thank you for your help.

    +
    0 Votes
    RationalGuy

    ... depending on what you're using as the key field to create the join in the new database. If you're using a value that exists in the current database, it will be pretty easy.

    Create three copies of the Excel worksheet that is the dump from the old dB, and name each of them with a name of one of the three tables in the new dB. Then, you can simply remove from the worksheets those columns that don't have a corresponding field in the new dB table.

    You mentioned that the old dB has 4 separate sets of fields to track reviews. In that case, on the "REVIEWS" worksheet, remove all columns except your key field and the 4 sets of review fields.

    For sake of discussion, I'll say for each review there are two fields. This means there are nine fields (columns) total:

    Column A is the key field
    Column B is the first field pertaining to REVIEW 1
    Column C is the second field pertaining to REVIEW 1
    Column D is the first field pertaining to REVIEW 2
    Column E is the second field pertaining to REVIEW 2
    Column F is the first field pertaining to REVIEW 3
    Column G is the second field pertaining to REVIEW 3
    Column H is the first field pertaining to REVIEW 4
    Column I is the second field pertaining to REVIEW 4

    Copy Column A and insert copies of it between the sets of REVIEW fields. You'll wind up with 12 fields:
    Column A is the key field
    Column B is the first field pertaining to REVIEW 1
    Column C is the second field pertaining to REVIEW 1
    Column D is the key field
    Column E is the first field pertaining to REVIEW 2
    Column F is the second field pertaining to REVIEW 2
    Column G is the key field
    Column H is the first field pertaining to REVIEW 3
    Column I is the second field pertaining to REVIEW 3
    Column J is the key field
    Column K is the first field pertaining to REVIEW 4
    Column L is the second field pertaining to REVIEW 4

    Create a copy of that worksheet for backup.

    Then, cut the values (remember not to copy the header row values) from columns D, E & F and paste them after the last row of values in columns A, B & C. Repeat, pasting the values for columns G, H & I and then J, K & L into columns A, B & C.

    Now you should just have three columns:
    Column A is the key field
    Column B is the first field pertaining to all reviews
    Column C is the second field pertaining to all reviews

    If there are more than two fields for each review, you can still get my basic process and adjust accordingly.

  • +
    0 Votes
    RationalGuy

    "Is there an easier way to do this than exporting the single table to Excel and manually fiddling with the data before importing?"

    Other ways may not necessarily be *easier* ways. You can build Access queries to "pre-fiddle" with your data. You can build some VBA code that connects to the old dB, queries the tables and INSERT queries the data into the new dB.

    Considering this is a one-off data move, Excel might be the most straightforward approach. It's very flexible, and you can easily move a lot of data around very quickly in a variety of manners,

    +
    0 Votes
    chrisd

    I was afraid of that answer. I couldn't think of another simple way to preserve the relationships between the three tables when I'm moving all the records from one table to multiple tables, etc. Not that this is going to be "simple", but it's easier than anything else I could think of.

    The annoying thing is that I've got to cut over this database while no one is in it, because they're constantly entering data into it. It's going to have to be a weekend job, I think.

    Thank you for your help.

    +
    0 Votes
    RationalGuy

    ... depending on what you're using as the key field to create the join in the new database. If you're using a value that exists in the current database, it will be pretty easy.

    Create three copies of the Excel worksheet that is the dump from the old dB, and name each of them with a name of one of the three tables in the new dB. Then, you can simply remove from the worksheets those columns that don't have a corresponding field in the new dB table.

    You mentioned that the old dB has 4 separate sets of fields to track reviews. In that case, on the "REVIEWS" worksheet, remove all columns except your key field and the 4 sets of review fields.

    For sake of discussion, I'll say for each review there are two fields. This means there are nine fields (columns) total:

    Column A is the key field
    Column B is the first field pertaining to REVIEW 1
    Column C is the second field pertaining to REVIEW 1
    Column D is the first field pertaining to REVIEW 2
    Column E is the second field pertaining to REVIEW 2
    Column F is the first field pertaining to REVIEW 3
    Column G is the second field pertaining to REVIEW 3
    Column H is the first field pertaining to REVIEW 4
    Column I is the second field pertaining to REVIEW 4

    Copy Column A and insert copies of it between the sets of REVIEW fields. You'll wind up with 12 fields:
    Column A is the key field
    Column B is the first field pertaining to REVIEW 1
    Column C is the second field pertaining to REVIEW 1
    Column D is the key field
    Column E is the first field pertaining to REVIEW 2
    Column F is the second field pertaining to REVIEW 2
    Column G is the key field
    Column H is the first field pertaining to REVIEW 3
    Column I is the second field pertaining to REVIEW 3
    Column J is the key field
    Column K is the first field pertaining to REVIEW 4
    Column L is the second field pertaining to REVIEW 4

    Create a copy of that worksheet for backup.

    Then, cut the values (remember not to copy the header row values) from columns D, E & F and paste them after the last row of values in columns A, B & C. Repeat, pasting the values for columns G, H & I and then J, K & L into columns A, B & C.

    Now you should just have three columns:
    Column A is the key field
    Column B is the first field pertaining to all reviews
    Column C is the second field pertaining to all reviews

    If there are more than two fields for each review, you can still get my basic process and adjust accordingly.