design advice for training session tracking

[Vargasman]

I've been tasked to create an application that can track weekly training sessions for over 300 personnel. I'm not sure how to set up the tables to get the desired results. I will need to be able to pull weekly reports based from date query, including the number of sessions attended and what sessions were attended. I think i would need to make several tables and related them together, but very new to access and am lost on where to start. here is an example of the data needed.

Name of personnel
Dates attended
Sessions attended
Number of required sessions
Number of attended sessions
Name of entering official

I would like to make it so it could be queried to show personnel that didn't meet there required number of sessions. The other part I foresee having problems with is trying to input the data into the database. We will have several people inputing the data daily for the different sessions. Also it would need to be easily adaptable for adding new personnel and removing old personnel no longer needing to be tracked. Thanks in advance for the help. i really appreciate it.

 

[KeithG]

For starters I thin you need the below tables

tblPersonnel
tblTrainingClass
tblTrainingClassSession
tblSessionBooking

 

[Vargasman]

Thanks for your quick relpy...How would you break out each table, collumn wise? At least for the following tables

tblTrainingClass
tblTrainingClassSession
tblSessionBooking

 

[KeithG]

Below is an example of how I would set it up with the information you have given. If I new all the exact business requirments I may set it up differently though.


tblTraingClass
ClassID
ClassName
Description


tblTrainingClassSessions
SessionID
ClassID
RoomNumber
MaxCapacity
Instructor
Date

tblSessionBookings
SessionID
PersonnelID

 

[gemma-the-husky]

think in terms of the data

all you really have are training_classes

these are staffed by teachers, and attented by students

so you need tables for

teachers
students
classes

assuming a class has one teacher you store the teacher id IN the class table

the last table you need is then a class_attendance table, which stores the students attending each class

(This is a fairly simple scenario - see below)

----------
you may find you also need perhaps a "class_type" table, which is again likely to be stored in the the class

----------
with this scenario you should be able to answer any query. eg how many students attended classes (maybe of a particular class type), which classes a given student attended, even which classes a student attended, taught by a given teacher.

at this point it then depends on whether the real world situation you are trying to model is more complex than this. Consider every little thing you might want to know - if the data model is right you should find a logical home for it - if not, you need to rethingk the model slightly.

one problem is that in any complex real world situation there are things that happen very infrequently, but which need to be catered for. If you don't allow for these at the start, it gets real hard fixing it later.

 

[Vargasman]

Thanks again for all of your help. I have all my tables set up in a functional manner.