Help with table design ?

[SFCMatthews]

I have several differnt departments with lots of employees attending training, and I need to be able to keep track of their attendance, and I have to be able to save a history of the data for the entire training cycle (training event and the date of training). What is the best way to do this in Access, keeping in mind that ease of data entry is a must, since I have a lot of departments and there employees to track? What I need is to be able to create a attendance roster for a department and update all of the employees at once if possible. Looking for a simple way to do this. I thank you all for your help.

I have attached a doc to give you a look at what I have done so far.

 

[PNGBill]

I imagine a Junction Table - say tblAttendance will serve the purpose of storing Attendance Data.
Fields could be: AttendanceID, EmployeeID, ClassID, ClassDate, ClassStartTime ...

Data stored would give you who taught what class on what day at what time. Of course your setup may well have differences but the concept may be similar.

How to Interface with this.. There are many ways to setup your Forms to input the data.

You could have the Teacher Login at the class pc which would cause an entry in tblAttendance.
You could have a form where you enter the Date and Time/Period (two controls) and then another Form will apear that is Populated with what classes are on and the Default teacher for each.
You then edit any differences, click a button and the updates are done to tblAttendance.

Of course there is a big difference between an overall idea and making it happen. Some Forms, vba code, sql's etc will be needed but it will work.

 

[SFCMatthews]

I tried to post my ERD but was unsuccessful don't know why.

 

[PNGBill]

Was it too big ??

 

[SFCMatthews]

Thanks did not think of that.

 

[PNGBill]

With your tables, do you have 4 Primary Keys on table SoliderRosterJoin ?
Appears to be a lot of tables. What is the purpose of TaskSubTaskJoin ?

Could you explain your tables ?

It is not good practice to have all your data on one table or data scattered over many. The idea is to have like data on each table.
eg,
tblPeople - this will hold the students and teachers bio data
tblCourses - this will hold the course description, regulataions etc related to that course.
tblClasses - Thses are the classes running now.
tblAttendance - this is Junction Table between tblClasses and tblPeople to keep track of attendance.

tblPeople would have 1 record for each Student and Teacher.
tblCourses will have one record for each Course eg MathsForDummiesOne is one record.
tblClasses would have new records each semester for the classes that will be available.
tblttendance will have a new record for each Teacher attending each class. The table will have many new records every day.

Of course, you may well know of more tables but tblpeople could hold all People records as long as one field allows for PeopleType "Student", "Teacher" etc.

Just some ideas.

 

[SFCMatthews]

With your tables, do you have 4 Primary Keys on table SoliderRosterJoin ?
Appears to be a lot of tables. What is the purpose of TaskSubTaskJoin ?

Could you explain your tables ?

It is not good practice to have all your data on one table or data scattered over many. The idea is to have like data on each table.
eg,
tblPeople - this will hold the students and teachers bio data
tblCourses - this will hold the course description, regulataions etc related to that course.
tblClasses - Thses are the classes running now.
tblAttendance - this is Junction Table between tblClasses and tblPeople to keep track of attendance.

tblPeople would have 1 record for each Student and Teacher.
tblCourses will have one record for each Course eg MathsForDummiesOne is one record.
tblClasses would have new records each semester for the classes that will be available.
tblttendance will have a new record for each Teacher attending each class. The table will have many new records every day.

Of course, you may well know of more tables but tblpeople could hold all People records as long as one field allows for PeopleType "Student", "Teacher" etc.

Just some ideas.

Ok,
tblMobUnit - this will hold all of the data for the unit that is training
tblSoldiersdata -this will hold all of the data for the soldiers
tblSoldiersRosterjoin - a Soldiers can be on many rosters and an a roster can have mamy soldiers on it
tblTrainingRoster - will hold the attendence records for the soldiers
tblTraiingEvent - holds the name of the lane and the date of the training
its the join between lanes and roster. Ok I see something wrong with this, a lane will have many roster but a roster will only be on one lane.
So i dont need the tblTrainingEvent.
tblTaskLane - holds all of the data for the training lane
tblTrainingUnit - holds the Training units and there POC data. A training unit can have many lanes but a lane will only be trained by one unit.
tbllanetaskjoin - is the join table for the many task that can be on a lane and the many lanes a task can be found on
tblTaskList - holds all of the main task that will be trainined on the lanes
tblTaskSubTaskJoin - is the join table for the many sub task that can be found in a main task and the many main task a sub task can be found in
tblSubTaskList - holds all of the data for the sub task that will be trainined in the main task

 

[PNGBill]

Sorry, I got dizzy reading the different subtasks etc.

Really scratch your head over this and I am sure you can reduce your table numbers some more.

tblSoldiersRosterjoin - a Soldiers can be on many rosters and an a roster can have mamy soldiers on it

No. A Soldier can be on more then one roster. That is all you need to consider.
A Roster only has One Record.
You collect a list of Soldiers on a given Roster by query.
tblSoldeiersRosterjoin will hold the records to get the result you need.
If a Soldier can be on more than one roster you will need a field to decide if the record is current or not. Simple solution is to query the last entry for each Soldier but in this case you can not but haw many records do you query for each soldier ? Records for this year only ? But what if a Soldier is removed form one of the rosters for this year ?
asy enough to sort out but best resolved with your knowledge of the environment.

How many tables deal with Tasks, subtasks and Lanes
Can Tasks and SubTasks be on the same table What is the difference between a Task and a SubTask ?
eg in a factory All inventory is on one table.
The fact that parta and partb can be used together to make partxyz is not an issue for tblParts.
tblSoldiers is one table for all soldiers and would include a field CurrentRank. Not a seperate tbale for Generals and another for Privates.
Again, you are best to resolve this but getting your tables sorted is the start of a good database.

Queries/SQL do all the work of providing the data required for Reports and Forms.
Don't assume tables have to be created to provide a one stop shop for a given report.

 

[SFCMatthews]

How many tables deal with Tasks, subtasks and Lanes
Can Tasks and SubTasks be on the same table What is the difference between a Task and a SubTask ?
eg in a factory All inventory is on one table.
The fact that parta and partb can be used together to make partxyz is not an issue for tblParts.
tblSoldiers is one table for all soldiers and would include a field CurrentRank. Not a seperate tbale for Generals and another for Privates.
Again, you are best to resolve this but getting your tables sorted is the start of a good database.

Queries/SQL do all the work of providing the data required for Reports and Forms.
Don't assume tables have to be created to provide a one stop shop for a given report.

To answer your question about lans, task, and sub task; A lane is where a task like weapons qualification would be trained. The task would be the act of qualifying with your weapons. The sub task is all of the little task it takes to qualify with said weapons. i.e.: weaponA has 15 subtask a soldier has to complete to be qualified on their individual weapon. weaponB has some of the same subtask and a few of its on and so on.
Thank for your time and help. I will take a deeper look into condensing the number of tables.

 

[PNGBill]

Just be mindeful the solution is not the number of tables it is a Normalised Database. Google for advice on this.

If Sub Tasks can be used on more then one Task thn you may well need two tables or Just have tblTasks and include one field to define if it is a Main Task or Sub Task.

A Junction Table can then define what sub tasks are used on what main task - even though they are on the same table.

Just like tblPeople has Parents and Children.

 

[SFCMatthews]

So what your saying about tblTask is to add a field and name it something like, TaskClass. Then define each class as Task and SubTask. I understand that but I am confused on how to use the junction table.