best way to display

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build on an up and running database which tracks all aspects
of training of our staff, including courses, when courses run, course
requirements and outstanding training.
What I am now trying to do is to flag what training is required for a
particular worker. The required training depends on the post that person is
working in and the location of that post.
For example a person working as a support worker at location1 will require
training in first aid, epilepsy and SCIP. A support worker working at
location2 will require training in first aid, moving and handling and care
management. A a project manager at location 2 will require same as support
worker plus management course.
I already have tables holding post details ie post ID, post title (support
worker, project manager etc), location (location1 or location2); a table of
employee details which tells what post ID that person is currently working
in; a table of courses ie course ID, course title, target audience etc.

Does anyone have any ideas the best way to go about this? I keep coming up
with ideas, but am not sure of the best way.
 
as a followup to this, I can explain further. A particular post will require
manycourses. A course is required for many posts. Therefore a many to many
relationship is created. How can I deal with this?
 
lynn atkinson said:
I am trying to build on an up and running database which tracks all
aspects of training of our staff, including courses, when courses
run, course requirements and outstanding training.
What I am now trying to do is to flag what training is required for a
particular worker. The required training depends on the post that
person is working in and the location of that post.
For example a person working as a support worker at location1 will
require training in first aid, epilepsy and SCIP. A support worker
working at location2 will require training in first aid, moving and
handling and care management. A a project manager at location 2 will
require same as support worker plus management course.
I already have tables holding post details ie post ID, post title
(support worker, project manager etc), location (location1 or
location2); a table of employee details which tells what post ID that
person is currently working in; a table of courses ie course ID,
course title, target audience etc.

Does anyone have any ideas the best way to go about this? I keep
coming up with ideas, but am not sure of the best way.
Click to expand...

It sounds to me like you already have tables like these:

People
PersonID (?)

Posts
PostID

Locations
LocationID (?)

Courses
CourseID

.... and you need a table like this:

CoursesRequired
CourseID
PostID
LocationID

The three fields I've listed under CourseRequired consitute the compound
primary key of the table. The presence of a record in this table for a
given CourseID, PostID, and LocationID, indicates that that course is
required for that post and location. This table is the "resolver" table
that represents the many-to-many relationship between Courses and the
combination of Posts and Locations. Presumably, somewhere you have a
table that records a person's post and location -- maybe these are in
the People table, maybe not. And also you will have a table that lists,
by person, what courses that person has completed.
 
Thanks for the reply. I am working hard to get my head round this and as I am
doing it, it is raising more questions. We have about 500 posts all with
individual training needs depending on the post title AND the location. Would
it not be sensible to put this information in the post details table? If so
how do I deal with the multiple courses for each post?

Have I lost it completely?

regards
 
lynn atkinson said:
Thanks for the reply. I am working hard to get my head round this and
as I am doing it, it is raising more questions. We have about 500
posts all with individual training needs depending on the post title
AND the location. Would it not be sensible to put this information in
the post details table? If so how do I deal with the multiple courses
for each post?
Click to expand...

It's not clear to me what your "post details" table represents, as
opposed to the "posts" table. Possibly your table names are confusing
me. If "post details" is uniquely keyed by the combination of PostID
and LocationID -- one record per post AND location -- and there can be
multiple courses required for that specific combination of post and
location, and this set of courses is not otherwise defined by post or
location alone, then the "post details" table is on the "one" side of a
one-to-many relationship with the table that lists required courses.
I'm calling that latter table "CoursesRequired".

Now, maybe PostDetails has a single, unique key of its own, like
PostDetailsID; in which case you can use that as the linking field to
CoursesRequired. I was just assuming that you would use the compound
key {PostID, LocationID} as the primary key of PostDetails, and hence
that you would use that same compound key as the foreign key in
CoursesRequired. Either way, you have one record in CoursesRequired for
each course that is required by a given "PostDetails" record; that is,
for each combination of {PostID, LocationID} that actually exists in
PostDetails and has required courses. That is how you represent the
multiple required courses -- as multiple records in this table.
Have I lost it completely?
Click to expand...

Not yet, but we're woprking on it. <g>
 
Sorry for the confusion, the table is called 'posts details' and holds the
following fields

PostID, post/role title, job description, hours p/w, location,

A table called 'worker details' holds name, address, workerID and current
postID.

So am I right in thinking that I create a 'courses required' table which
lists all the courses required for postID 20. then display this as a subform
on the 'posts details' form?

This makes sense, but it seems a lot of work as all support workers, for
example, require the same basic training. Can I combine this information in
the following way?

a post title is 'support worker' or 'project manager'.
All support workers require to have training in course A, B and C regardless
of where they work (location).
All project managers require training in X and Y regardless of their location.

A worker of any title who works at location 1 requires to be trained in
course D,E and F.

therefore a support worker working in location 1 requires training in course
A,B,C,D,E and F
A project manager working in location 1 requires training in courses X,Y,
D,E and F

Does this make sense?

regards
 
lynn atkinson said:
Sorry for the confusion, the table is called 'posts details' and
holds the following fields

PostID, post/role title, job description, hours p/w, location,

A table called 'worker details' holds name, address, workerID and
current postID.

So am I right in thinking that I create a 'courses required' table
which lists all the courses required for postID 20. then display this
as a subform on the 'posts details' form?

This makes sense, but it seems a lot of work as all support workers,
for example, require the same basic training. Can I combine this
information in the following way?

a post title is 'support worker' or 'project manager'.
All support workers require to have training in course A, B and C
regardless of where they work (location).
All project managers require training in X and Y regardless of their
location.

A worker of any title who works at location 1 requires to be trained
in course D,E and F.

therefore a support worker working in location 1 requires training in
course A,B,C,D,E and F
A project manager working in location 1 requires training in courses
X,Y,
D,E and F

Does this make sense?
Click to expand...

Okay, so now we have these primary entities:

PostTitles
PostTitle ("support worker", "project manager")

Locations
LocationID or LocationName

PostDetails
PostID
PostTitle
Location
(other fields)

WorkerDetails
WorkerID
WorkerName
Address
PostID

Courses
CourseID

There is a list of courses that are required for each post title, so we
need a table for that:

PostTitleCoursesReq
PostTitle
CourseID

You'd edit this table with a subform on a main form that is based on
PostTitles.

There is a list of courses that are required for each location, so we
need a table for that:

LocationCoursesReq
LocationID
CourseID

You'd edit this table with a subform on a main form that is based on
Locations.

It's not clear to me now whether there are courses that are required
only for particular post title in a particular location. If so, then
you need another table:

PostLocationCoursesReq
PostTitle
LocationID
CourseID

For the moment, I'm going to interpret what you've said as indicating
that you don't need this third table; that the combination of courses
required for the post title with the courses required for the location
covers the complete set of required courses.

Now, if you need to know the courses required by each post, based on the
post title and location, you need a union query:

SELECT PostDetails.PostID, PostTitleCoursesReq.CourseID
FROM PostDetails INNER JOIN PostTitleCoursesReq
ON PostDetails.PostTitle = PostTitleCoursesReq.PostTitle
UNION
SELECT PostDetails.PostID, LocationCoursesReq.CourseID
FROM PostDetails INNER JOIN LocationCoursesReq
ON PostDetails.LocationID = LocationCoursesReq.LocationID;

This query combines the coursed required by both the title and the
location. If you use it as the recordsource of a subform on the
PostDetails form, linked to the main form by the PostID, it will show
only the courses required by that post. You may want to add the Courses
table into the query, so that you get the course descriptions, too. My
guess is that you'll have to requery the subform any time you change the
post title or location for the post on the main form.

Note that this query, because it's a union query, is not updatable. But
that's okay, because this list of courses isn't based on the post
itself, but rather on tables that are related to attributes of the post.
 
Back
Top