Revamp DB

[Karen]

I have a DB that I acquired from someone at work. This DB
is used to track employee training on company procedures.
The DB is not set up properly. Example: In the employee
table, there are not separate fields for employee name.
She has the entire name in one field. (example: Smith,
John) Also, this DB is used for 3 different departments.
Instead of creating a department table, she created 3
different databases for each department. (Note: Every
table is the same in all 3 databases)

What I want to do is: (and I hope this can be done - this
DB is driving me nuts) Start from scratch and create a DB
with first and last name in separate fields. Create a
department table so there are not 3 separate databases for
each department. I would want to import all the existing
data from all three databases into the new DB, while
maintaining the integrity of the data.

Can this be done? If so, how do I go about this?
Below are the current tables in the DB.

Employee Table
EmployeeName
EmployeeNumber
DateOfHire
TerminationDate

Training Grid Table
ProcedureNumber
TrainingType
EmployeeName
DocumentationDate

Training Type Table
TrainingType

Procedure Table
ProcedureNumber
ProcedureName
ProcedureType

PLEASE HELP - I would greatly appreciate ANYONE'S help!
Thank you

 

[Jamie Richards]

Hi Karen,

A couple of suggestions for you. First go to Dev Ashish's Access Web
(http://www.mvps.org/access/strings/str0001.htm) and get info on parsing
strings, that will help you with separating out the names.

Secondly...Hmmmm. Why not copy all the existing data from 2 of the
databases into the tables of the third database (make back-ups first). Add
the Departments table you spoke of and assign an ID of some sort to each
department in that table - make it the primary key. Then create the
DepartmentID field as a foreign key in every table that you need to relate
department data to. Use SQL to insert the appropriate "new" department ID's
against each record, based on whatever criteria you can find to identify a
record as belonging to a particular department.

The big problem will arise if you cannot identify which records to assign to
your various departments. If you find that to be the case, BEFORE you merge
the three databases, create a dummy "DepartmentID" field in the current
database tables and assign it a value, but make sure every record has that
same value applied. Do this for each of the three databases. Then copy all
the data from 2 of the db's into the matching tables in the one remaining.
(This is sounding dreadful isn't it?)

Example:

[tblEmployees]
EmployeeNumber (PK)
DepartmentID (FK) <-- add this field and assign it a value...The
number 1 will do (for ALL records)!
FirstName
LastName
DateOfHire
TerminationDate

Do this for each table that will need relating to the new Departments table.
When you do the next database, change the number you add to something else
(2 sounds good!). Finally, do the same for the third database. If you
don't want to use those ID's for the departments you can change them to
something more meaningful later. The important thing is you now have some
way of identifying which department owns what records.

Kind of messy, but it might be a strating point?

Jamie