combination of data question?

[Guest]

Further to my post of yesterday titled 'best way to display' in Forms, I
received a helpful reply but in trying to implement this, I got confused!

What I am trying to do is to list training requirements of a particular
position (post) in the company. The training required depends on the post
worked and the location of that post.

For example
Location 1 requires a person to be trained in course1, course3

location 2 requires a person to be trained in course 1 and course4

A support worker requires to be trained in course 2 and course 5
a project manager requires to be trained in course 2 and 7

Therefore a support worker working in location 1 will require training in
course1, course3, course2 and cours5

a project manager in location 1 will require course2 and course7 as well as
course1 and 3.

Can I add these training requirements to the post table? Would this be
sensible as the post is described as post title (support worker) and location
and therefore a particluare postID will have particular requirements.

However, there requirements will be multiple ie many courses for a
particular post. How would I deal with this?

If using a resolver table combining the courseid, postid and locationid is
the way to go, as suggested yesterday, how do I go about this?

Hope someone can help as I am tying myself in knots

regards

 

[Steve Schapel]

Lynn,

There is no reason to include the training requirements to the Posts
table (and there are a number of reasons not to!) If I understand you
correctly, the training requirements are essentially tagged to certain
jobs and certain locations, and each course can be a requirement for
more than one job and/or location. So your table structure needs to
reflect this reality. I think the simplest way is like this...

- Make a table, let's call it TrainingRequirements, with 2 fields,
Course and RequiredFor. So, using your example, the data would look
like this:
Course RequiredFor
1 Location 2
1 Location 1
2 Project Manager
2 Support Worker
3 Location 1
4 Location 2
5 Support Worker
7 Project Manager

- Whenever you need to know the training requirements for any given
post, use a Query which includes both your Posts table (which I have
asumed has fields named Person, Post, and Location) and also the
Training Requirements table. The SQL view of such a query would look
something like this...
SELECT Posts.Person, LocationRequirements.Course
FROM Posts, LocationRequirements
WHERE ((LocationRequirements.RequiredFor=[Posts].[Post]) Or
(LocationRequirements.RequiredFor=[Posts].[Location]))

This query will list a Project Manager at Location 1 as requiring
Courses 1, 2, 3, and 7.

 

[Steve Schapel]

Sorry, I used a differnt table name in my SQL example than in my
description, so to avoid confision, the SQL was supposed to be...
SELECT Posts.Person, TrainingRequirements.Course
FROM Posts, TrainingRequirements
WHERE ((TrainingRequirements.RequiredFor=[Posts].[Post]) Or
(TrainingRequirements.RequiredFor=[Posts].[Location]))