multiple tables to update 1 record

[ROMADOZ]

Hello,
I am building a database to track training. I have 4 separate groups of training (4 tables). The items on these tables change based on directions from higher. I have a table and corresponding form for personnel. In this table (and form) you choose what job area you are in, from there I need access to use the info I just chose to select the proper job tasks listed in the group of training table and add them to the individual persons record so they can be seen. I need to track the date that the training was completed by individual. Help?

-ROMADOZ

 

[neileg]

I'm not sure what your question is. However, it is almost certain that you should have one table and not four for training.

 

[ROMADOZ]

help

I am under the impression that I need 4 tables for the different types of training because they are owned by different entities in my organization, and that they are all constantly changing. I need the owner of eah type to be able to go in and change the training items at will. Once they change those training items, the changes are reflected on the records of the personnel that the training applies to. Each person in my organization will need at least some tasks from each type of training, but never all of each type. I also need to be able to record a training completion date one each individual's record.
Right now, I have a subform on my personnel information form that is linked to a field that chooses the job type. By choosing the job type, it changes the tasks required. I just need to be able to record a completion date for each training item. I need it stored in the individual person's record.
With that said, I guess my question is how? How do I take information from one of the training tables (the tasks required) apply that info to an individual person's record, and then store a completion date for each of those training items?

Thank you.

 

[georgedwilkinson]

You don't need 4 tables...1 table for each atomic type of thing you deal with is sufficient, with a discriminator column to further identify it's type. You limit access to an individual by using a query as the basis for your forms. The query limits the record set to the individual user's recordset.

Having the data correctly normalized and objectified will greatly simplify all tasks you need to undertake.

I realize this probably doesn't make sense to you but am not really sure what your level of expertise is. I am thus unsure how to give you a clearer picture. Please feel free to post follow-up questions.

 

[ROMADOZ]

continued assistance

Not sure if I follow. In each type of training, it is broken down again by (in the 1st case) by what job you hold. The table has all the tasks for all the jobs. Here is what it looks like:

The PK is an autonumber
Job (comes from the Jobs table)
Task

My next one has different tasks based on what team you are assigned to

PK Autonumber
Team (comes from the teams table)
Task

The third is the same except that it is broken down by division of hte organization... identical except for the table that holds the different divisions

The last is common training for everyone in the organization. It is the same minus the subgrouping.

Is this what you mean... or did I miss the concept? Thank you for the help.

-ROMADOZ

 

[neileg]

This is actually quite a complex application if you are just starting out. I think you are expecting Access to work like a spreadsheet, but it doesn't. Users never see tables in a well designed application.

You need one table for training taks with a field that identifies whether they are job, team, division or global. If you only want users to see one type, you can use a query that selects just that one type. If your organisation decides to add a 5th type of training, you simply define a new category. With 4 separate tables you would need to add a 5th table and fully rebuild your application.

 

[jdraw]

Not sure if I follow. In each type of training, it is broken down again by (in the 1st case) by what job you hold. The table has all the tasks for all the jobs. Here is what it looks like:

The PK is an autonumber
Job (comes from the Jobs table)
Task

My next one has different tasks based on what team you are assigned to

PK Autonumber
Team (comes from the teams table)
Task

The third is the same except that it is broken down by division of hte organization... identical except for the table that holds the different divisions

The last is common training for everyone in the organization. It is the same minus the subgrouping.

Is this what you mean... or did I miss the concept? Thank you for the help.

-ROMADOZ

Here is a reference that may help with design.
http://r937.com/relational.html