complex select query

[MES via AccessMonster.com]

I am creating a database that tracks when hospital employees take certain
required education courses. I have a query currently that lists what
employees have not taken a specified course. However, I need this query to
be a little more involved, and I am stuck. There are certain courses that
are required of certain job codes, so I need to figure in that logic - for
example, list only those employees that are required to take the specified
course, and not those that are not required to take it. (Each employee has a
job code.)

Does anyone have any suggestions on even where to begin?

Thanks in advance.

 

[Guest]

hi,

I have a suggestion...

Perhaps you could add a column to the job table called "job code". Then you
could use the job code of each employee to relate it to all jobs with that
specific job code in the job table.

Let me know if you need further help...

geebee

 

[Steve Schapel]

MES,

Do you have the "required courses" information in a table somewhere in
your database. I think you will need it, in order to do what you want
here. In other words, a list of which courses are required for which
job codes.

 

[MES via AccessMonster.com]

Do you mean a matrix showing for each job code what course is required?

MES,

Do you have the "required courses" information in a table somewhere in
your database. I think you will need it, in order to do what you want
here. In other words, a list of which courses are required for which
job codes.

I am creating a database that tracks when hospital employees take certain
required education courses. I have a query currently that lists what

[quoted text clipped - 8 lines]

Thanks in advance.

 

[Steve Schapel]

MES,

Well, yes. You want to process data on the basis of whether a required
course has been taken. And the meaning of "required" depends on the job
code. The only way your query can "know" how to process the data is on
the basis of data. So you need to have data available which shows which
courses are required for which job codes. Does that make sense? I'm
not sure what you mean by "matrix" though. I think you could use a
table with 2 fields:
JobCode
CourseID
.... and then it's just a list of all courses associated with all jobs.

Once you have that list, I expect it will be a simple matter to join
this table into your existing query, and you should have the results you
want.

 

[MES via AccessMonster.com]

What about if I have multiple job codes that are required to take one course?
In this table, would I have the following:

JobCode_Course
JobCode
Course

as the fields? Then I could query a job code, and it would give me a list of
courses required for that job code?

 

[Steve Schapel]

MES,

Yes, that is correct. I am not sure what you are proposing with the
JobCode_Course field... is this an Autonumber field? I wouldn't
normally think this was necessary, but equally does no harm.