How Do I record my employees many courses and awards?

[Martin hopkins]

Dear All,

Sorry for this problem, but its cuasing me some grief.

Currently I have 123 employess who over the years have attended some 400
courses and received 100 awards, altogether. Currently I have a manual
paperwork system that tells me which employee has attended what course and
received which award. I would like to do this in access.

I have created a database in Access 2000 with several tables, with the
personnel table containing details on staff address etc as they are unique
to each staff. Also created courses table with each employee having a
record. But how do I link this to an award table and input the data into a
form.

I dont want to create an award table with all the present awards issued
because it could expand with new awards etc. I would like to input from a
form the required data into a table. For example, if employee A goes on a
new course, then from the form I can add this new course to the master list
of courses and assign him as having attended. The same applies to the award
area.

Any help would be appreciated.

Martin Hopkins

 

[Rick B]

The proper way to do this is to have several tables...

tblEmployee
EmployeeNumber (PK)
FirstName
LastName
Address
etc.

tblAwards
AwardNumber (PK)
AwardName


tblClasses
ClassNumber (PK)
ClassName
NumberOfHours
etc.

tblEmployeeAwards
EmployeeNumber
AwardNumber
DateIssued
Comments

tblEmployeeClasses
EmployeeNumber
ClassNumber
DateCompleted
Score
Comments



This is a classic many-to-many relationship.

 

[Rick B]

Martin:

I did not address your statement....

"...then from the form I can add this new course to the master list of
courses and assign him as having attended"


That would be something that you could build into your form. You'd use the
"not in list" event. You can look at the hlp files, or read previous posts
on the topic if yo need more details. In short, youd have the user select a
class from the list. If it was a new class (not in list) you'd pop up a
small form where the user could make the needed entries and have the class
added to the table.

Lots of ways to do this, just depends on how you end up building the
interface. My original response only dealt with the table structure, not
the interfaces neede to use the tables.

Typically, you'd have a form for the employees, and a subform for their
classes, and another subform for thier awards.

You will need to post back specific questions if you have any when you start
building your forms.