Add New Field to DB

[Karen]

I have a DB that someone who once worked here created. The DB is not set up
properly and I don't have the time to create another one and learn how to
import all this data. Therefore, I have to work with what I have. This DB is
used to track our company traning procedures for each employee. There are no
primary keys set up and the employee table has the first and last names
combined in one field - to give you an idea.

Below are the table structures:

Employee Table:
EmployeeName
EmployeeNumber
DateOfHire
TerminationDate

Procedures Table:
ProcedureNumber
ProcedureName
ProcedureType

Training Grid Table:
ProcedureNumber
TrainingType
EmployeeName
DocumentationDate

Training Type Table:
Training Type

We have changed our Procedure Numbers and they are now called NT Numbers.
We are in the middle of changing over all the procedure numbers to the new
numbers. In the mean time, I have to track this info in our database. As far
as I know, a query should have been created to be used for data entry and the
previous employee is using the "Training Grid" table for that purpose. How
should this DB be changed so I can add a new field for "NT Number" and have
each NT Number correspond in there respective record with the old procedure
number? I hope I am explaining this so it is understood. Please let me know
if I'm not - I really need to start entering the new numbers.

ANY help would be greatly appreciated

 

[KARL DEWEY]

As a work around you can just add the new field in the Procedures Table.

I do not see dates when training was received. Some training is reoccuring
like every year. The table needs to have a field for that. List all your
training requirements and figure out the common calendar cycle to use. If
nothing is less than a year then use increments of years except for one-time
training that will have an interval of 0 (zero).

Some training records are required to kept for an extended period by law.
If your Human resources or trraining branch is not maintaining hard copy in
the personnel folder then the database would be your legal records.

I will post more if you want.

 

[Karen]

Thank you for your help - I added a new field to the Procedures Table - Now
where do I go from there? Won't there be a problem if the relationships are
not set up properly?

There is a date field "Documentation Date" in the Training Grid table. What
I do is choose the training type and then I add the date for that record.

Thank you again, Karen

 

[KARL DEWEY]

Your post did not mention any relations. Do any exist?

YourTraining Grid Table has EmployeeName. If there were relations I would
expect not to find name but EmployeeNumber in the table.

Below is the table structure for a training database my company used for
several years before moving on to a web-Orcale database.

The TEMP EmployeeInfo table is how I connected weekly to HR for new hires,
terminations, and people going or coming off casual/leave of absence.
For a new hire I ran an append query that added all training marked as
required for anyone in their department. Their manager was given a list of
the training for them and asked to mark any training they did not need. That
training was removed from their record.

Table: CourseStatus tbl
Columns
Name Type Size
CourseStatusId Long Integer 4
CourseStatus Text 50


Table: CourseType tbl
Columns
Name Type Size
CourseTypeID Long Integer 4
CourseType Text 50


Table: DPINDEX
Columns
Name Type Size
ID Long Integer 4
DP NUMBER Text 14
DP TITLE Text 255
BASIC DATE Date/Time 8
REVIEW DATE Date/Time 8
DEPT# Text 4


Table: EMPBRIEF
Columns
Name Type Size
EMPNAME Text 20
EMP_NUMBER Text 9
BRIEFED Text 9
NEXT1 Date/Time 8
DEPT Text 6
BLDGNO Text 8
DCODE Text 7
FNAME Text 12
MNAME Text 12


Table: PPE LIST
Columns
Name Type Size
ppe Text 50


Table: Report Level
Columns
Name Type Size
Level Text 5


Table: Switchboard Items
Columns
Name Type Size
SwitchboardID Long Integer 4
ItemNumber Integer 2
ItemText Text 255
Command Integer 2
Argument Text 50


Table: tbl CBT Course Description
Columns
Name Type Size
CTL# Long Integer 4
Course Duration Text 2
Audiance Memo -
Prerequisites Memo -
Course Aim Text 255
Learning Objectives Memo -
Topics Covered Memo -
Course Incorporates Text 50


Table: tbl Certification
Columns
Name Type Size
Number Long Integer 4
CourseID Text 50
Position Title Text 100
CourseTitle Text 50
RenewalPeriod Single 4


Table: tbl DeptInfo
Columns
Name Type Size
Dept# Text 50
Range Text 50
DeptTitle Text 50
DeptManager Text 50


Table: tbl EduCourses
Columns
Name Type Size
EduCourseID Long Integer 4
Course# Text 50
CourseTitle Text 150
StartDate Date/Time 8
EndDate Date/Time 8
CreditHours Long Integer 4
Grade Text 50
Cost Currency 8
CourseStatus Long Integer 4


Table: tbl Edu-Degree
Columns
Name Type Size
EduDegID Long Integer 4
EmpDegId Long Integer 4
EduCourseID Long Integer 4


Table: tbl EduDegrees
Columns
Name Type Size
DegreeID Long Integer 4
DegreeType Text 50


Table: tbl EduUniversities
Columns
Name Type Size
UniversityID Long Integer 4
University Text 50
UniversityShort Text 50


Table: tbl Emp-Cert
Columns
Name Type Size
Number Long Integer 4
SSN Text 50
DateStart Date/Time 8
DateComp Date/Time 8
Cert Yes/No 1
Required Yes/No 1
Trainer Initials Text 3
Employee Initials Text 3


Table: tbl Emp-Degree
Columns
Name Type Size
EmpDegID Long Integer 4
SSN Text 50
UniversityID Long Integer 4
DegreeID Long Integer 4
Major Text 50
DateEntered Date/Time 8
GradDate Date/Time 8
DegreeComp Yes/No 1


Table: tbl Emp-Equipment
Columns
Name Type Size
SSN Text 50
OMC# Long Integer 4
StartDate Date/Time 8
EndDate Date/Time 8
CourseStatusID Long Integer 4
PkgStatID Long Integer 4
Certificate Yes/No 1
EntryDate Date/Time 8
Trainer Initials Text 3
Employee Initials Text 3


Table: tbl Emp-ETR
Columns
Name Type Size
SSN Text 9
DateRecd Date/Time 8
ReviewDate Date/Time 8
Update Date/Time 8
CertReq? Yes/No 1
O/M Yes/No 1


Table: tbl Emp-ExtCSR
Columns
Name Type Size
XAN# Text 200
SSN Text 200
Comp Date Date/Time 8


Table: tbl Emp-Gov Page: 20
Columns
Name Type Size
GAN Text 200
SSN Text 50
Class# Text 50
TLN# Text 50
StartDate Date/Time 8
EndDate Date/Time 8
CourseStatusId Long Integer 4
Evaluation Received? Yes/No 1
Certificate Received? Yes/No 1


Table: tbl EMP-IAN
Columns
Name Type Size
ID Long Integer 4
SSN Text 50
IAN# Text 50
DateStart Date/Time 8
DateComp Date/Time 8
Certificate Yes/No 1
Required Yes/No 1
Trainer Initals Text 3
Employee Initials Text 3


Table: tbl EMP-IAN 1
Columns
Name Type Size
ID Long Integer 4
SSN Text 50
IAN# Text 50
DateStart Date/Time 8
DateComp Date/Time 8
Certificate Yes/No 1
Required Yes/No 1
Trainer Initals Text 3
Employee Initials Text 3


Table: tbl EmployeeInfo
Columns
Name Type Size
SSN Text 9
LastName Text 25
FirstName Text 25
MI Text 2
Suffix Text 5
Group# Text 7
Dept# Text 50
CSCShort Text 8
LOA/CSL Yes/No 1
Term Yes/No 1
NonEmployee Yes/No 1
BLDG Text 6
BLDG DATE Date/Time 8
DEPT DATE Date/Time 8
Hire Date Date/Time 8


Table: tbl Emp-OMC
Columns
Name Type Size
SSN Text 50
OMC# Long Integer 4
StartDate Date/Time 8
EndDate Date/Time 8
CourseStatusID Long Integer 4
PkgStatID Long Integer 4
Certificate Yes/No 1
EntryDate Date/Time 8
Trainer Initials Text 3
Employee Initials Text 3


Table: tbl Emp-OMC Temp
Columns
Name Type Size
SSN Text 9
OMC# Long Integer 4
StartDate Date/Time 8
EndDate Date/Time 8
CourseStatusID Long Integer 4
PkgStatID Long Integer 4
Certificate Yes/No 1
EntryDate Date/Time 8
Trainer Initials Text 3
Employee Initials Text 3


Table: tbl Equipment
Columns
Name Type Size
Program# Text 50
OMC# Long Integer 4
Title Text 150
Dept# Text 10
PkgStatID Long Integer 4
CourseTypeID Long Integer 4
Test Equipment Flag Yes/No 1
Equipment Flag Yes/No 1


Table: tbl ExtCSR
Columns
Name Type Size
XAN# Text 200
EntryDate Date/Time 8
CourseTitle Text 200
CourseProvider Text 200
CourseTypeID Long Integer 4
CompDate Date/Time 8
CourseStatusID Long Integer 4


Table: tbl Government Training
Columns
Name Type Size
GAN Text 200
Course# Text 50
CourseTitle Text 50
DateReq Date/Time 8


Table: tbl Internal
Columns
Name Type Size
IAN# Text 50
EntryDate Date/Time 8
CourseTitle Text 50
CourseProvider Text 100
CourseTypeID Long Integer 4
CourseStatusId Long Integer 4
Comments Memo -
StartDate Date/Time 8
CompDate Date/Time 8


Table: tbl Internal 1
Columns
Name Type Size
Number Long Integer 4
CourseID Text 50
Position Title Text 100
CourseTitle Text 50
RenewalPeriod Single 4


Table: tbl OMC
Columns
Name Type Size
Program# Text 50
OMC# Long Integer 4
Title Text 150
IssueDate Date/Time 8
RevDate Date/Time 8
Dept# Text 10
Revision# Long Integer 4
PkgStatID Long Integer 4
CourseTypeID Long Integer 4
Comments Memo -
Nomenclature Text 50
PreReq Text 250
LWC Flag Yes/No 1
Desk Procedure Yes/No 1
Operating Instruction Yes/No 1


Table: tbl PkgStatus
Columns
Name Type Size
PkgStatID Long Integer 4
PkgStatus Text 50


Table: tbl TEMP Safety Tour
Columns
Name Type Size
SSN Text 9
LastName Text 25
FirstName Text 25
MI Text 2
Suffix Text 5
Group# Text 7
Dept# Text 50
HR Dept Text 3
DEPT DATE Date/Time 8
BLDG Text 6
HR Bldg Text 6
BLDG DATE Date/Time 8
LOA/CSL Yes/No 1
DateComp Date/Time 8
EMP_STAT Text 3
Update Integer 2
Update Bldg Integer 2
Term Yes/No 1


Table: TEMP EmployeeInfo
Columns
Name Type Size
SSN Text 9
LastName Text 25
FirstName Text 25
MI Text 2
Suffix Text 25
Group# Text 7
Dept# Text 50
CSCShort Text 50
LOA/CSL Yes/No 1
Term Yes/No 1
NonEmployee Yes/No 1
BLDG Text 5
BLDG DATE Date/Time 8
DEPT DATE Date/Time 8
HIRE_DATE Date/Time 8
Minimum TNG Yes/No 1


Table: TEMP tbl Certification
Columns
Name Type Size
Number Long Integer 4
CourseID Text 50
Position Title Text 100
CourseTitle Text 50
RenewalPeriod Single 4

 

[Karen]

Actually there are relationships set up, but they are one-to-one.

Employee & Training Grid have the Employee Name as one-to-one
Training Grid & Training Type have the Training Type as on-to-one
Training Grid & Procedures Table have the Procedure Number as on-to-one

I will have to add a field for my new procedure numbers in the Procedures
Table.
Then I use a form for data entry, which references a query for the record
source.

I do have a few concerns.
Eventually, the old numbers will phase out.
Right now I have a training record that has a new procedure number and not
an old procedure number. I have to leave the old & new number fields on the
form during the transition. In this situation, I don't want to leave the old
number field blank and fill in the new number field. Someone may think that
the user forgot to fill in that field. What should be done in a situation
like this? Right now the vast majority have the old & new number.
HELP!

 

[KARL DEWEY]

How is it that you have one-to-one? Do not some employees require more than
one type of training class?

Put a dash ( - ) in the old number field.

Can you at sometime in the future return to using a single field even though
you have two number schemes?

 

[Karen]

This DB was created by someone who used to work here and now I am responsible
for the DB.

When I open the relationship window, there is only a line connecting the
fileds I mentioned. Isn't that a one-to-one? It's not a one-to-many (with
the infinity symbol)

To answer your questions:

Do not some employees require more than one type of training class? Yes
they do

Can you at sometime in the future return to using a single field even though
you have two number schemes? We may want to always keep the old number. If
that changes, it won't be for many years.

ANY suggestions? Thank you again for your help

 

[KARL DEWEY]

I think your Employee & Training Grid should have the EmployeeNumber as
one-to-many.
Two things to do after you make a backup copy of ther database.

Add EmployeeNumber to the Training Grid and update to fill it in.

Delete the relation between Employee & Training Grid. Make the
EmployeeNumber the key field in the Employee table. Then create a
one-to-many relations Employee & Training Grid on the EmployeeNumber. Select
the update but not the delete unless you know all the ramifications.