Compiling new database

[Guest]

I am attempting to compile data on the employees in my facility. I would like
to be able to build databases that would enable me to track their
in-services. In long term care certain in-services are mandated by the
state.Would this work for the first table?
Employee ID ( autonumber)
Last name
First name
Department
Date Hired
Title
Current employee ( yes/no)
Orientation Date
I would like the second table ( and the first table) to have the primary key
of Employee ID. This is where I am confused, should I repeat last name, first
name fields? Should I autonumber Employee ID again?
This table would have additional fields of named in-services such as;
Abuse/Neglect
Body Mechanics
Domestic Violence
HIV
Medication Errors
etc. Each fields data type would be date. This way I could track when the
employee attended the in-service and be able to determine what they were
lacking for a period of time. Thanks in advance for your assistance.

 

[John Vinson]

I am attempting to compile data on the employees in my facility. I would like
to be able to build databases that would enable me to track their
in-services. In long term care certain in-services are mandated by the
state.Would this work for the first table?
Employee ID ( autonumber)
Last name
First name
Department
Date Hired
Title
Current employee ( yes/no)
Orientation Date

That looks good - if each employee has one and only one orientation
date, anyway.

I would like the second table ( and the first table) to have the primary key
of Employee ID.

Ummm... probably NOT. That would give you the ability to enter one,
and only one, inservice for each employee; the Primary Key is by
definition unique within the table, meaning that you can have only one
record with that employee's ID. Instead you should have an InserviceID
primary key (perhaps an autonumber), and a Long Integer EmployeeID -
NOT the primary key - as a foreign key to the Employee table.

This is where I am confused, should I repeat last name, first
name fields? Should I autonumber Employee ID again?

Neither. The name information should exist in the Employee table *and
noplace else*; you can link to that table using the EmployeeID to find
it.

This table would have additional fields of named in-services such as;
Abuse/Neglect
Body Mechanics
Domestic Violence
HIV
Medication Errors
etc. Each fields data type would be date.

This is called "committing spreadsheet" and it's a venial sin,
punishable by being compelled to read about normalization, e.g. in the
Database Design on Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

This way I could track when the
employee attended the in-service and be able to determine what they were
lacking for a period of time. Thanks in advance for your assistance.

A much better design would be to use THREE tables:

Employees <as above>

InServices
InServiceID <Autonumber, Primary Key>
Subject <Text, e.g. Abuse/Neglect>
<other description of this in-service as an entity>

Attendance
EmployeeID <Long Integer, foreign key to Employees>
InServiceID <Long Integer, foreign key to InServices>
AttendenceDate Date/Time <when this employee attended this
in-service>
<other fields about this employee in this class, e.g. comments,
satisfactory or unsatisfactory completion, feedback, etc.>


John W. Vinson[MVP]

 

[Jeff Conrad]

in message:

This is called "committing spreadsheet" and it's a venial sin,
punishable by being compelled to read about normalization, e.g. in the
Database Design on Jeff's site:

Hey now!