Unnormalized Data Import to Normalized Database

[Guest]

The final hour is approaching quickly for me. I have by Friday to make my
database live. I will be starting a new job the following Monday, so the
deadline is absolute.
I've never had to do this before, and I could really use some help!

Here's the deal. The purpose of the database is to keep track of assigned
batch numbers to users. The old database consists of one table. The new
database consists of 7 tables. In the new database schema, there are
basically two "main tables" an employee table and an assigned batches table.
I am wondering how I can go about creating a query that updates both tables
at once. For instance, the employee contact information in the employee
table needs to be there to assign it an Employee Id which is the foreign key
reference in the AssignedBatches table.

I was thinking about just importing the data in basically field by field,
but if I could do this in a step approach it be so much faster.

Tips or suggestions?

TIA!
AA

 

[John Nurick]

Usually in this situation most of the work is in cleaning the data.
Assuming I've understood your description, the general idea is to start
by building a query that extracts employee data from the old database,
one record per employee, and using it as the basis for an append query
that populates the new Employees table.

Unless the old database includes the EmployeeID field (and maybe even
then) this process will almost certainly produce bogus near-duplicate
records resulting from inconsistent data entry into the non-normalised
table (e.g. records that mention "Joe Smith" and "J Smith" may be
referring to the same person). So the next stage is to clean the
Employees table to eliminate these.

Once you have an satisfactory table of employees, each with a unique
EmployeeID, add an EmployeeID field to the old table. Create an update
query that joins the two tables on an appropriate set of the employee
fields and updates the new field in the old table. This will give you
EmployeeIDs in the old records that match records in the new employees
table. Further queries and in some cases manual editing will be required
to ensure that every record in the old table has a valid EmployeeID
ready to be imported into the new AssignedBatches table.

 

[Guest]

Thanks for your reply, John. I am glad to know that I am on the right track.
This was my thought process for how to add employees to the new db. It's
going to be really fun cleaning and editing the data HA! I know for sure
it'll be a bumpy ride.

I may post again if I get stuck on something and I hope you can keep in
touch.

Thanks much : )
AA

 

[John Nurick]

A couple of useful things to remember:

You can find records in the old table that haven't yet been matched to
the new with the criterion
WHERE EmployeeID IS NULL

Using the LIKE operator in query criteria can help find records with
trivial differences:
WHERE LastName = "Smith" AND FirstName LIKE "John*"
gets you John Smith, John P Smith, John Peter Smith - and,
unfortunately, Johnathan Smith.

For more subtle pattern matching, see
http://www.mvps.org/access/modules/mdl0063.htm

 

[Guest]

Things went well yesterday. I've updated the old table to include FK
references to each of the new tables, except for AssignedBatches and I
appended much of the data from the old table to the new tables. I just have
to update a few of the employee records and then I can append data to the
AssignedBatches table and I should be good to go. I was more worried about
it than I needed to be...the process is fairly straightforward. Even still,
I'm glad I posted here and got some feedback from you. It helped me to stick
to one plan and not screw anything up too badly!

Thanks for all your help!

 

[Guest]

I'm glad it's going well.