Apprenticeship Training Matrix

[theDBguy]

I haven't followed this thread in detail; but just in case it hasn't been mentioned, I don't recommend using "placeholder" records (if that's what you're doing).

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to...

thedbguy.blogspot.com

 

[LarryE]

I haven't followed this thread in detail; but just in case it hasn't been mentioned, I don't recommend using "placeholder" records (if that's what you're doing).

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to...

thedbguy.blogspot.com

The OP does not want to use the ACCESS relational model anyway.

 

[theDBguy]

The OP does not want to use the ACCESS relational model anyway.

Interesting. Would Excel be a better tool then?

Note: Again, I haven't read through the whole thread, so I apologize if that was covered already.

 

[LarryE]

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

Here is a file of your data presented using relational modeling. Both students have been assigned for Year 1 only, but queries for Years 2, 3, and 4 are available. This is the only way that ACCESS can process and present your information correctly. I use VBA code, not macros. Of course, you will need to create input forms to add and edit other data. If you wish, you could create filters on the sub-form to filter study years or any other data. This will give you a proper start for you apprenticeship system.

 

[hazmatth]

Hi, could anyone please take a look at my database.

On the main form I have 4 text boxes, all of which have a control source on the subform.

my objective is to have the percentage complete for each year.

If anyone could take a look and advise, this would be appreciated.

 

[mike60smart]

Hi
I only took a quick look at your design and stopped.

The problem you have is that your tables are not designed correctly.

Each table should have an Autonumber PK and NOT Text Data Types.
The related FK should be a Number Data Type and NOT Text.
It is recommended not to Hard Codes values at table level, these should be Records in another table.
It is recommended that you do not use Attachment fields.
You should not have spaces in field names.

 

[LarryE]

Hi, could anyone please take a look at my database.

On the main form I have 4 text boxes, all of which have a control source on the subform.

my objective is to have the percentage complete for each year.

If anyone could take a look and advise, this would be appreciated.

I converted your data into relational format and created tables and a form. Using ACCESS this way is the only way it is going to work for you. You are attempting to use ACCESS like a spreadsheet and it will not perform like that. Download the Apprentice.accdb file above. That will give you a start in the right direction.

 

[plog]

You backtracked your database. In post #17 you attached a database that correctly implemented the StudentsTrainingTbl table. But the latest one you posted you have made StudentsTrainingTbl essentially a clone of MechatronicsTrainingListTbl (plus the student ID). Why?

In a relational database you don't store redundant data. In StudentsTrainingTbl you didn't put fields for all student data (FirstName, LastName, DateofBirth, etc.) but you did put all the fields from MechatronicsTrainingListTbl. That's not correct. Relationally, StudentsTrainingTbl needs to sit between MechatronicsTrainingListTbl and StudentsTbl. Since many students can take many trainings and many trainings can be taken by many students you need a table to sort all of that out and that's StudentsTrainingTbl's job. It is a junction table:

Many-to-many (data model) - Wikipedia

en.wikipedia.org

It doesn't need data specific to the training, it doesn't need data specific to the student. It just needs the ID values from each of those tables to make a match and line students up with their training.

 

[victorlindh]

Use the database in post #17