Table Structure and Relationships

[Gary]

I have a database I'm creating to track employee training
attendance using Access2000. I'm relatively new to Access
design, and just want to confirm that the way I have
created this is the best way.

I have one table [eetbl] to track detailed employee
information such as name, hire date, supervisor, etc.

The second table [clstbl] tracks detailed course
information such as course start date and end date, course
name, duration, course code, etc.

I have also created a third table to track which employee
attended which class [attentbl]- and this final table is
only tracking [eeID] and [clsID].

Ideally I would like to be able to query the [attentbl] by
clsID and have it return all employees who attended- as
well as query by eeID and return all courses attended. The
ability to add multiple employees simultaniously would be
a highly beneficial addition.

Is this possible using this design? Any suggestions or
alternatives would be welcome!

Thank you!
Gary

 

[Allen Browne]

Hi Gary.

You are on the right track with your employee table, class table, and
employee-in-class table (junction table, with foreign keys to the other
two).

With that structure, you can create a main form for the class, with a
subform bound to your "attentbl". Add the employees who attended to each row
of the subform (using a combo to select the employee if desired).

To achieve your goal of being able to automatically add the employees who
were supposed to attend a class, you need to know much more than your 3
tables give you. A full-blown system that tracked which subject were offered
when, by whom, who was enrolled (which gives you who was supposed to
attend), who actually attended, and when they have completed the subjects
and therefore the entire course would have tables like this:

Course: one record for each certificate/diploma offerred.
CourseID
CourseName

Subject: one record for each subject taught
SubjectID
SubjectName

SubjectInCourse: junction table between Courses and Subjects.
CourseID
SubjectID

SubjectOffered: one record for each time a subject is offered.
SubjectOfferedID
SubjectID
StartDate
Supervisor

Enrol: students enrolled in a SubjectOffered
EnrolID
SubjectOfferedID
StudentID

Class: classes for a SubjectOffered
ClassID
SubjectOffered
Date
Instructor

Attendee: students who attended a class:
ClassID
StudentID
AttendStatus (present/absent/late/excused/...)

Student: table of students

Staff: table of staff

(That doesn't include tracking assessments.)

 

[Gary]

Allen-
Thank you so much for your reply. This was extremely
helpful!

~Gary

-----Original Message-----
Hi Gary.

You are on the right track with your employee table, class table, and
employee-in-class table (junction table, with foreign keys to the other
two).

With that structure, you can create a main form for the class, with a
subform bound to your "attentbl". Add the employees who attended to each row
of the subform (using a combo to select the employee if desired).

To achieve your goal of being able to automatically add the employees who
were supposed to attend a class, you need to know much more than your 3
tables give you. A full-blown system that tracked which subject were offered
when, by whom, who was enrolled (which gives you who was supposed to
attend), who actually attended, and when they have completed the subjects
and therefore the entire course would have tables like this:

Course: one record for each certificate/diploma offerred.
CourseID
CourseName

Subject: one record for each subject taught
SubjectID
SubjectName

SubjectInCourse: junction table between Courses and Subjects.
CourseID
SubjectID

SubjectOffered: one record for each time a subject is offered.
SubjectOfferedID
SubjectID
StartDate
Supervisor

Enrol: students enrolled in a SubjectOffered
EnrolID
SubjectOfferedID
StudentID

Class: classes for a SubjectOffered
ClassID
SubjectOffered
Date
Instructor

Attendee: students who attended a class:
ClassID
StudentID
AttendStatus (present/absent/late/excused/...)

Student: table of students

Staff: table of staff

(That doesn't include tracking assessments.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a database I'm creating to track employee training
attendance using Access2000. I'm relatively new to Access
design, and just want to confirm that the way I have
created this is the best way.

I have one table [eetbl] to track detailed employee
information such as name, hire date, supervisor, etc.

The second table [clstbl] tracks detailed course
information such as course start date and end date, course
name, duration, course code, etc.

I have also created a third table to track which employee
attended which class [attentbl]- and this final table is
only tracking [eeID] and [clsID].

Ideally I would like to be able to query the [attentbl] by
clsID and have it return all employees who attended- as
well as query by eeID and return all courses attended. The
ability to add multiple employees simultaniously would be
a highly beneficial addition.

Is this possible using this design? Any suggestions or
alternatives would be welcome!

Thank you!
Gary

.