Database Changes

[Karen]

I posted the below message on the board Saturday and I
haven't received any help at all. Just wondering if it's
too complicated, I didn't explain myself, etc. I just need
some type of direction and I was hoping to get some help.

PLEASE HELP!

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

 

[Jeff Boyce]

Karen

Yes, you can combine DBs. One approach would be to create your new DB
(empty), then link to the existing DBs to do a series of append/update
queries to "load" it.

Hope this gets you started...

Jeff Boyce
<Access MVP>

 

[Lynn Trapp]

Karen,
By all means make the changes. To bring the databases together you could
simply link the three databases into your new one and do UNION queries to
create the new tables. Then use the following functions to extract the parts
of the names to insert into the new FirstName and LastName fields:

LastName: Left([EmployeeName],InStr([EmployeeName],",")-1)
FirstName: Mid([EmployeeName],InStr([EmployeeName],",")+2)

This should get you started.

 

[Karen]

Thank you for your help Lynn - How do I link the
databases? - If I want to keep all the same fields within
the tables, I would only have to separate the last name &
first name in the new DB. I'm not familiar with Union
Queries to create the new tables - Could you give me some
tips on how to start? I'll check the Access HELP
Thank you

-----Original Message-----
Karen,
By all means make the changes. To bring the databases

together you could simply link the three databases into
your new one and do UNION queries to create the new
tables. Then use the following functions to extract the
parts of the names to insert into the new FirstName and
LastName fields:
LastName: Left([EmployeeName],InStr([EmployeeName],",")-
1)
FirstName: Mid([EmployeeName],InStr([EmployeeName],",")+2)

This should get you started.

 

[tina]

yes, it's do-able, but a little tricky because you'll have to create new
primary key values for some of the records in at least one table -
Employees - and transfer those new values to the junction table -
TrainingGrid. depending on what the records are in the TrainingTypes and
Procedures tables, you may have to do the same thing there, too.

i've had to do this many times, and you're right - it's a real pain in the
behind! i don't think i can explain how i do it in print, though. if you're
in the continental U.S., i'm willing to try to talk you thru it on the
phone. if you want to go that route, email me with a phone number, time
zone, and time frame when i can reach you.

ttacTAKEOUTALLCAPITALcess1 at yahooLETTERSdot THESEcomTOO

 

[Lynn Trapp]

Karen,
You link databases through File > Get External Data > Link.

To do the kind of UNION query you problably need would be something like
this. After you link the tables from the three databases. Then create an SQL
statement like this:

Select Field1, Field2, Field3....FieldN
From Table1
UNION
Select Field1, Field2, Field3...FieldN
From Table2
UNION
Select Field1, Field2, Field3...FieldN
From Table3;

Save that query and create a make table query like this:

Create Table NewTable As
Select *
From YourUnionQuery;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thank you for your help Lynn - How do I link the
databases? - If I want to keep all the same fields within
the tables, I would only have to separate the last name &
first name in the new DB. I'm not familiar with Union
Queries to create the new tables - Could you give me some
tips on how to start? I'll check the Access HELP
Thank you

-----Original Message-----
Karen,
By all means make the changes. To bring the databases

together you could simply link the three databases into
your new one and do UNION queries to create the new
tables. Then use the following functions to extract the
parts of the names to insert into the new FirstName and
LastName fields:
LastName: Left([EmployeeName],InStr([EmployeeName],",")-
1)
FirstName: Mid([EmployeeName],InStr([EmployeeName],",")+2)

This should get you started.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm