database relationships question for a staff training register (1 Viewer)

Griffin1985

New member
Local time
Today, 04:15
Joined
Apr 7, 2017
Messages
4
Im looking for a point in the right direction on how about to do the following.

I have the following tables
Staff members(a table of staff memebers)
Training(a table of of trainings)


with the training, i would like to have it return what staff were current(staff have a status field) at the time of the training, and then select whether or not they attended the training.

Im just a bit confused about the best way to go about this, im playing around with a many to many relationship subform but im not getting what i really want.

Please bear in mind ive been teaching myself over the last couple of months in my spare time so Im quite the noob.

if you need anymore details or examples to clarify what im talking about please let me know


Thanks
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,611
This is one of the biggest obstacles in databases. Master it and you are well on your way to being a good developer. This concept has many names, wikipedia calls it an associative entity (https://en.wikipedia.org/wiki/Associative_entity) it is known in the database world as a junction table.

When you have 2 tables that have a many to many relationship (many staff members can be in many trainings) you need a table between those two to sort out which staff goes to which training. That's a junction table.

StaffTable
StaffID, StaffName, StaffDOB, etc.
2, John, 2/3/2001
14, Sally, 12/1/2000

TrainingTable
TrainingID, TrainingName, TrainingDate, etc.
6, Sexual Harrassment, 1/3/2017
8, CPR, 2/9/2017

TrainingAttendance
StaffID, TrainingID
2, 6
2, 8
14, 8

That third table directs who went were. From it you should be able to tell that John took both trainings and Sally only took CPR. Formwise, your main form could either be based on TrainingTable or StaffTable and the subform would be based on TrainingAttendance.
 

Griffin1985

New member
Local time
Today, 04:15
Joined
Apr 7, 2017
Messages
4
Its been a bit to wrap my head around, but I have a junction table setup.

This junction table is called "TBLTRAININGREGISTER"

I've then set up a form based on "TBLTRAININREGISTER", and the subform on the training table is sourced from that form.

One thing ive come across is on the junction table form I cant add the same staff member twice because Ive set referential integrity, but on the subform I can?

But i was more so wondering is how i could set this subform to auto populate with the current staff, and then select whether or not they attended.

I'm thinking I may have to create a drynamic subform that is unbound, which then queries "current" staff, and then saves the attendance to the table??
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,611
But i was more so wondering is how i could set this subform to auto populate with the current staff, and then select whether or not they attended.

First let me tell you why this wouldn't be the best way to do that (and then I will tell you how to). It would be best to only keep attendance in TBLTRAININGREGISTER that way, if the person is in the table, they attended, if not they didn't. Your method (everyone gets a record) will require you to touch the table once per record to mark attendance, so will mine (only add records for attendees). So why not leave out records that tell you nothing? Especially since Staff can be added and removed which means you will ultimately end up with people in Staff table, not in TBLTRAININGREGISTER.

Now, here's how to accomplish your method--with an APPEND query. You build a query based on Staff that inserts a record for every Staff member into TBLTRAININGREGISTER. From a form, you simply click a button to run that query and it does all the work of adding all those records.
 

Griffin1985

New member
Local time
Today, 04:15
Joined
Apr 7, 2017
Messages
4
Much appreciated, the main reason I want to mark attendance for all current staff is so I can easily see attendance rate for the trainings, and easily query trainings staff have attended and havent attended.

I know its a bit redundant, but i figure its the easiest way to accomplish those queries beyond just who actually attended.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 23, 2006
Messages
15,361
Griffin1985,

Do yourself a favor and put Access to the side while you do some research on relational database concepts. You need a plan and a specification for what you are trying to build.
If you start with a clear description of the business and the processes involved, you will find that designing the related database follows a general procedure.
And if you start by working through a tutorial or 2 from RogersAccessLibrary, you will learn that procedure by experience. And what you learn can be used with any database.

The bottom line is you need a clear statement of WHAT you are trying to; then a decision of HOW you will do that in Access.

Database relationships are based on your business rules/facts. They are not arbitrary lines between tables.

Here is a link to info that has been recommended for other posters facing similar situation.
Database design and related info.

Good luck.
 

Users who are viewing this thread

Top Bottom