Apprenticeship Training Matrix (1 Viewer)

[hazmatth]

Hi all,

I am in the process of creating an apprenticeship training matrix, essential a log of apprentices and a list of training that they are required to complete at different stages throughout their apprenticeship.

- I have a table that is a basic profile of the apprentice
- I have a table that lists all the tasks to complete throughout a four year period

My end goal is to have a form that has multiple pages: profile, year 1, 2, etc, on the yearly pages I want to list the tasks required for that year and a tickbox showing completion status.

Every apprentice has the same required tasks to complete so I need to essential assign all the tasks when a new apprentice profile (record) is added.

Can anyone help, I have the layout all sorted but I am struggling to link to table of task records to a single apprentice record

 

[Minty]

Show us what you have.
Tables and relationships please. Pictures or a srtipped down version of the database.

I think your description of the design is raising some potential red flags.

I don't think I would have a page per year of training (what happens if you suddenly have a fifth year etc.), I think I would simply have a form that displayed a year based on a drop down choice. That choice can be variable depending on the type of apprenticeship? (Not all might be 4 years some might only be 2 or 3 for instance.

 

[theDBguy]

Hi. Welcome to AWF!

Did you create a junction table to store the assigned task for each apprentice? For data entry, you'll probably use a form/subform setup.

 

[hazmatth]

Hi, my DB is still in very early stages, I have attached for a better understanding

 

[hazmatth]

Show us what you have.
Tables and relationships please. Pictures or a srtipped down version of the database.

I think your description of the design is raising some potential red flags.

I don't think I would have a page per year of training (what happens if you suddenly have a fifth year etc.), I think I would simply have a form that displayed a year based on a drop down choice. That choice can be variable depending on the type of apprenticeship? (Not all might be 4 years some might only be 2 or 3 for instance.

ApprenticesTbl:
UserID (Primary key)
FirstName
LastName
Email
SiteName
TrainingYear
ManagersFirstName
ManagersLastName
ManagersEmail

TrainingTbl:
TrainingID (Primary Key)
ApprenticeshipType
TrainingYear
TrainingSection
TrainingTitle
TrainingDescription

ApprenticesTrainingTbl:
UserID
TrainingID
ApprenticeshipType
TrainingYear
TrainingSection
TrainingTitle
TrainingDescription

There are two types of apprenticeship programs, both are 4 years long with set training requirements per year. The list of training is the same for every apprentice dependent on the which of the two programs they are on

 

[mike60smart]

I would imagine that in 2023 you would start a number of Students in the Apprentice Programme.
Then in 2024 you would start a different number of Students on the Programme.

Is this your process?

 

[plog]

Here's the biggie:

Mechatronics Training List needs to be split into 2 tables. 1 table holds all the generic information about a training section (title, description, etc.). And then another table needs to be made to hold the training specific to a Student. That will include the Student ID, the Training ID and all the fields currently in [Mechatronics Training List] that hold data that go with a students training (as opposed to just the training itself).


Then random other notes:

1. No spaces/special characters in names. Just makes coding/querying harder. Eliminate the spaces in table and field names.

2. [Training Year] should be numeric and probably so should [Education Year]. You know it represents a year, no need to prefix every value with "Year" or suffix [Education Year] the same way.

3. Probably should use a manager table. If managers are not unique (multiple students can have same manager) then make a table to store that info and just store the ID of that record in Students. Otherwise you open yourself up to typos in their data and other incosistencies (Robert, Bobby, Bob, etc.).

4. Need another table for Schedule data. When you feel the need to suffix field names with numbers its time for a new table for all that data. Then for every Schedule you add a new record in that new table. That way if only 2 are needed you don't have wasted space and if you get 6 Schedule you can accomodate that without a structure change.

5. [Status] should probably be a drop down. You can't trust users. They are going to mistype "absent" or "present" or whatever the valid statuses are. Give them a drop down so its consistent.

6. Don't just use "ID", prefix them with the table name. It just makes it easier when you query because otherwise you might have a bunch of valid ID fields and not be certain which one you are looking at.

 

[LarryE]

What does Education Year and Site mean in the Students table?

 

[hazmatth]

I would imagine that in 2023 you would start a number of Students in the Apprentice Programme.
Then in 2024 you would start a different number of Students on the Programme.

Is this your process?

Yes every year new apprentices will start. After 4/5 year period the first lot of apprentices can be removed

 

[hazmatth]

What does Education Year and Site mean in the Students table?

Start year* and Site is location, there are multiple sites that the apprentices are based

 

[hazmatth]

Here's the biggie:

Mechatronics Training List needs to be split into 2 tables. 1 table holds all the generic information about a training section (title, description, etc.). And then another table needs to be made to hold the training specific to a Student. That will include the Student ID, the Training ID and all the fields currently in [Mechatronics Training List] that hold data that go with a students training (as opposed to just the training itself).


Then random other notes:

1. No spaces/special characters in names. Just makes coding/querying harder. Eliminate the spaces in table and field names.

2. [Training Year] should be numeric and probably so should [Education Year]. You know it represents a year, no need to prefix every value with "Year" or suffix [Education Year] the same way.

3. Probably should use a manager table. If managers are not unique (multiple students can have same manager) then make a table to store that info and just store the ID of that record in Students. Otherwise you open yourself up to typos in their data and other incosistencies (Robert, Bobby, Bob, etc.).

4. Need another table for Schedule data. When you feel the need to suffix field names with numbers its time for a new table for all that data. Then for every Schedule you add a new record in that new table. That way if only 2 are needed you don't have wasted space and if you get 6 Schedule you can accomodate that without a structure change.

5. [Status] should probably be a drop down. You can't trust users. They are going to mistype "absent" or "present" or whatever the valid statuses are. Give them a drop down so its consistent.

6. Don't just use "ID", prefix them with the table name. It just makes it easier when you query because otherwise you might have a bunch of valid ID fields and not be certain which one you are looking at.

Thank you for the reply and I agree with everything you have said.

I am only having a practice run at the minute and will clean everything up as I do the actual db, its been a while so I am very rusty.

The main issue I am having is this:

I want a table for apprentices, so a new record of an apprentices can be added whenever.
I want a table of required training, this training list is the same for every apprentice.

The part I am struggling with is to assign the training list to every apprentice that is added. I know this needs to be in another table but I just cant seem to get my head around it. The rest of what you have highlighted above I am able to do.

 

[plog]

The part I am struggling with is to assign the training list to every apprentice that is added.

That is actually quit simple--its done with an APPEND query. When you set your tables up as I described, breaking your training data into 2 tables, you run a query to when a new student is added that populates the student training table with all the values in the training table.

 

[hazmatth]

That is actually quit simple--its done with an APPEND query. When you set your tables up as I described, breaking your training data into 2 tables, you run a query to when a new student is added that populates the student training table with all the values in the training table.

Would you mind taking a look please, I think I have completed everything you said above but cannot get the append query to work how I want it to.

 

[plog]

The below query will accomplish what I mean, but can be made more user friendly:

INSERT INTO StudentsTrainingTbl ( TrainingID, UserID )
SELECT MechatronicsTrainingListTbl.TrainingID, [Enter User ID] AS Expr1
FROM MechatronicsTrainingListTbl;

You can set it up so that the prompt isn't required and automatically gets passed from the form.

 

[LarryE]

Based upon what you have described, the design below is how I might design your system:

1. Each Appenticeship Year (2023, 2024 etc.)
2. Has multiple Training Sections being taught
3. Each Training Section has multiple individual classess (TrainingTitles)
4. Each Training Tile may be taught in multiple Training Years (Year 1, Year 2 etc.)
5. Each Training Year has a Training Result for each Student.

 

[hazmatth]

The below query will accomplish what I mean, but can be made more user friendly:

INSERT INTO StudentsTrainingTbl ( TrainingID, UserID )
SELECT MechatronicsTrainingListTbl.TrainingID, [Enter User ID] AS Expr1
FROM MechatronicsTrainingListTbl;

You can set it up so that the prompt isn't required and automatically gets passed from the form.

Thank you for your help so far. I have got the query set up

Based upon what you have described, the design below is how I might design your system:
View attachment 112736

1. Each Appenticeship Year (2023, 2024 etc.)
2. Has multiple Training Sections being taught
3. Each Training Section has multiple individual classess (TrainingTitles)
4. Each Training Tile may be taught in multiple Training Years (Year 1, Year 2 etc.)
5. Each Training Year has a Training Result for each Student.

Thank you for replying but this isn't what I am looking for. I have attached an updated version of my db. In this version I am trying to get a checklist of the training required each year onto seperate tabs on the form.

 

[hazmatth]

The below query will accomplish what I mean, but can be made more user friendly:

INSERT INTO StudentsTrainingTbl ( TrainingID, UserID )
SELECT MechatronicsTrainingListTbl.TrainingID, [Enter User ID] AS Expr1
FROM MechatronicsTrainingListTbl;

You can set it up so that the prompt isn't required and automatically gets passed from the form.

Thanks for you help so far. I have got the query to do what you said but cannot get the name to come from the form, I am required to input the name

 

[plog]

Create a button on your student form, and add this code to it's onclick event:

Private Sub btn_CreateTraining_Click()
    ' will create a training schedule for student
    
    Dim str_SQL As String       ' will hold sql for INSERT query to create training schedule
    
    str_SQL = "INSERT INTO StudentsTrainingTbl ( TrainingID, UserID ) "
    str_SQL = str_SQL & "SELECT MechatronicsTrainingListTbl.TrainingID, '" & Me.UserID & "' AS UserID "
    str_SQL = str_SQL & "FROM MechatronicsTrainingListTbl;"
    ' compiles SQL to INSERT all records for student training
    
    DoCmd.RunSQL (str_SQL)
    ' executes SQL and creates records
    
    End Sub

The INSERT query you have is no longer needed, it is in the code above. Notes:

1. Use this on a test copy of your database first, it is going to make changes. Test it then move it to the real one.

2. There is no protection for multiple clicks. If you (or a user) clicks it a second (third, fourth, etc.) time, it will happily keep making training records for that student. You probably need to put some protection on it so it can't do that.

 

[hazmatth]

Create a button on your student form, and add this code to it's onclick event:

Private Sub btn_CreateTraining_Click()
    ' will create a training schedule for student
   
    Dim str_SQL As String       ' will hold sql for INSERT query to create training schedule
   
    str_SQL = "INSERT INTO StudentsTrainingTbl ( TrainingID, UserID ) "
    str_SQL = str_SQL & "SELECT MechatronicsTrainingListTbl.TrainingID, '" & Me.UserID & "' AS UserID "
    str_SQL = str_SQL & "FROM MechatronicsTrainingListTbl;"
    ' compiles SQL to INSERT all records for student training
   
    DoCmd.RunSQL (str_SQL)
    ' executes SQL and creates records
   
    End Sub

The INSERT query you have is no longer needed, it is in the code above. Notes:

1. Use this on a test copy of your database first, it is going to make changes. Test it then move it to the real one.

2. There is no protection for multiple clicks. If you (or a user) clicks it a second (third, fourth, etc.) time, it will happily keep making training records for that student. You probably need to put some protection on it so it can't do that.

Thank you so much for you help so far, I have got the code working.

I'm signing out for the weekend, would you mind if I asked a few more questions via this forum next week when I am back

 

[plog]

I'm always checking this, but really this site has a lot of people who can help. Just post your questions and you will get good advice.