database design issue

[MES via AccessMonster.com]

I am having trouble figuring something out and hoping someone out there might
be able to help.

I have built an Access database for a hospital education department to track
education activities that employees have taken and/or need to take. I have
built into the structure a means of requiring a specific job code to take a
specific activity, or employees from a specific department to take an
activity. I have tables set up - Employee, JobCodes, Depts, JC_Requirements,
and Dept_Requirements. Each employee has a Dept_Num and a JC_Num. These
fields are linked to the Dept and JC tables. Each table is linked to a
separate requirements table (please be assured that this database is
normalized). So if an employee has a specific job code and/or a specific
department, they would be required to take an activity that is required for
either the job code or department. For example, if JC_Num is required to
take Course_Num, then JC_Required =1 (has to take), else equals zero (doesn't
have to take). Further if Dept_Num is required to take Course_Num, then
Dept_Required =1, else 0. So in my database, an employee can be required to
take and activity either because of their job code or because of the
department they are in.

My set-up seemed to work fine until we realized that there are certain
activities that a job code would be required to take, but only if that job
code was in a specific department. For example, only an RN in Maternity
department would be required to take the Neonatal Resuscitation activity, not
RN's in other departments in the hospital. Originally, there were so few of
these exceptions that I was able to add code (in the form of an if/then
statement) to a query. (If JC_Num = (RN) and Dept_Num = (Maternity), then
Required = True, Else Required = False). However, we have more and more
exceptions, and I have found that I only have so much space in the field in
the query for this code, so I am now at the limit. I tried assigning aliases
to shorten the amount of space I'm taking up in the field, but I again filled
it up.

I am looking for advice on what to do next. I think that I need to alter my
database structure somewhat to accomodate this, but I can't think of any way
to do this. Would anyone know how to handle this?

Thanks in advance for your help.

MES

 

[Amy Blankenship]

I am having trouble figuring something out and hoping someone out there
might
be able to help.

I have built an Access database for a hospital education department to
track
education activities that employees have taken and/or need to take. I
have
built into the structure a means of requiring a specific job code to take
a
specific activity, or employees from a specific department to take an
activity. I have tables set up - Employee, JobCodes, Depts,
JC_Requirements,
and Dept_Requirements. Each employee has a Dept_Num and a JC_Num. These
fields are linked to the Dept and JC tables. Each table is linked to a
separate requirements table (please be assured that this database is
normalized). So if an employee has a specific job code and/or a specific
department, they would be required to take an activity that is required
for
either the job code or department. For example, if JC_Num is required to
take Course_Num, then JC_Required =1 (has to take), else equals zero
(doesn't
have to take). Further if Dept_Num is required to take Course_Num, then
Dept_Required =1, else 0. So in my database, an employee can be required
to
take and activity either because of their job code or because of the
department they are in.

My set-up seemed to work fine until we realized that there are certain
activities that a job code would be required to take, but only if that job
code was in a specific department. For example, only an RN in Maternity
department would be required to take the Neonatal Resuscitation activity,
not
RN's in other departments in the hospital. Originally, there were so few
of
these exceptions that I was able to add code (in the form of an if/then
statement) to a query. (If JC_Num = (RN) and Dept_Num = (Maternity), then
Required = True, Else Required = False). However, we have more and more
exceptions, and I have found that I only have so much space in the field
in
the query for this code, so I am now at the limit. I tried assigning
aliases
to shorten the amount of space I'm taking up in the field, but I again
filled
it up.

It seems that really you need a single table of the requirements.

So you might have

tblRequirement
RequirementID
JC_Num
Dept_Num
Course_Num
JC_and_Dept

So now you only have one table to query. You return courses where JC_Num
and Course_Num or Dept_Num and Course_Num when JC_And_Dept is false, but
JC_Num and have to be equal to the employee's in order to return Cours_Num
when JC_and_Dept is true.

HTH;

Amy

 

[MES via AccessMonster.com]

Okay - thanks for the response - I think I understand.

One concern though - I currently have 58 activities in my database, 323 job
codes, and 127 departments. If I create a unique field for each
JC/Dept/Activity combination and identify the requirement true or false, I
will have over 2 million records. Is that what you mean?

I am having trouble figuring something out and hoping someone out there
might

[quoted text clipped - 41 lines]

filled
it up.

It seems that really you need a single table of the requirements.

So you might have

tblRequirement
RequirementID
JC_Num
Dept_Num
Course_Num
JC_and_Dept

So now you only have one table to query. You return courses where JC_Num
and Course_Num or Dept_Num and Course_Num when JC_And_Dept is false, but
JC_Num and have to be equal to the employee's in order to return Cours_Num
when JC_and_Dept is true.

HTH;

Amy

 

[Amy Blankenship]

Okay - thanks for the response - I think I understand.

One concern though - I currently have 58 activities in my database, 323
job
codes, and 127 departments. If I create a unique field for each
JC/Dept/Activity combination and identify the requirement true or false, I
will have over 2 million records. Is that what you mean?

First, you do *not* want to create unique fields for this. That would be
horrendous, but I suspect you meant records.

You would only have a record for each combination that results in an
employee's need to take a course.

So for instance if your courses were

1 Underwater Basket Weaving
2 Pedicures for Fun and Profit
3 Dr. Seuss as Literary Genius
4 The Geometry of Round Tuits

And you had the departments

1 Human Resources
2 Accounting
3 Engineering

and the Job Codes

1 Clown
2 Pig Farmer
3 Shaman
4 Peanut Butter Sandwich Maker

then, if all Clowns had to take "Dr. Seuss as Literary Genius" then you'd
have a record

JC_num Dept_Num Course_Num JC_and_Dept
1 Null 3 False

if all HR employees had to take "Pedicures for Fun and Profit" then it would
look like this:
JC_num Dept_Num Course_Num JC_and_Dept
Null 1 2 False

and finally, if all Pig Farmers in Accounting had to take "The Geometry of
Round Tuits", you'd need
JC_num Dept_Num Course_Num JC_and_Dept
2 2 4 True

HTH;

Amy

 

[MES via AccessMonster.com]

Yes, that helps.

Yes, I did mean records.

Thanks a lot.

Okay - thanks for the response - I think I understand.

[quoted text clipped - 3 lines]

JC/Dept/Activity combination and identify the requirement true or false, I
will have over 2 million records. Is that what you mean?

First, you do *not* want to create unique fields for this. That would be
horrendous, but I suspect you meant records.

You would only have a record for each combination that results in an
employee's need to take a course.

So for instance if your courses were

1 Underwater Basket Weaving
2 Pedicures for Fun and Profit
3 Dr. Seuss as Literary Genius
4 The Geometry of Round Tuits

And you had the departments

1 Human Resources
2 Accounting
3 Engineering

and the Job Codes

1 Clown
2 Pig Farmer
3 Shaman
4 Peanut Butter Sandwich Maker

then, if all Clowns had to take "Dr. Seuss as Literary Genius" then you'd
have a record

JC_num Dept_Num Course_Num JC_and_Dept
1 Null 3 False

if all HR employees had to take "Pedicures for Fun and Profit" then it would
look like this:
JC_num Dept_Num Course_Num JC_and_Dept
Null 1 2 False

and finally, if all Pig Farmers in Accounting had to take "The Geometry of
Round Tuits", you'd need
JC_num Dept_Num Course_Num JC_and_Dept
2 2 4 True

HTH;

Amy

 

[MES via AccessMonster.com]

I have another question about this. I have the tblRequirements set up as you
suggested, I am having trouble linking to employee and creating a query now
to select a specific employee's requirements.

I select an employee, then I need to also list all of the activities that are
required for this employee, whether by job code or department or both. Any
ideas on how to do this?

Okay - thanks for the response - I think I understand.

[quoted text clipped - 3 lines]

JC/Dept/Activity combination and identify the requirement true or false, I
will have over 2 million records. Is that what you mean?

First, you do *not* want to create unique fields for this. That would be
horrendous, but I suspect you meant records.

You would only have a record for each combination that results in an
employee's need to take a course.

So for instance if your courses were

1 Underwater Basket Weaving
2 Pedicures for Fun and Profit
3 Dr. Seuss as Literary Genius
4 The Geometry of Round Tuits

And you had the departments

1 Human Resources
2 Accounting
3 Engineering

and the Job Codes

1 Clown
2 Pig Farmer
3 Shaman
4 Peanut Butter Sandwich Maker

then, if all Clowns had to take "Dr. Seuss as Literary Genius" then you'd
have a record

JC_num Dept_Num Course_Num JC_and_Dept
1 Null 3 False

if all HR employees had to take "Pedicures for Fun and Profit" then it would
look like this:
JC_num Dept_Num Course_Num JC_and_Dept
Null 1 2 False

and finally, if all Pig Farmers in Accounting had to take "The Geometry of
Round Tuits", you'd need
JC_num Dept_Num Course_Num JC_and_Dept
2 2 4 True

HTH;

Amy

 

[Amy Blankenship]

I have another question about this. I have the tblRequirements set up as
you
suggested, I am having trouble linking to employee and creating a query
now
to select a specific employee's requirements.

I select an employee, then I need to also list all of the activities that
are
required for this employee, whether by job code or department or both.
Any
ideas on how to do this?

Note, this is by the seat of my pants without having access to your db, so
as a first pass it may turn out to be wrong, but this is where I'd start...

Open the Query builder

Show your Employees table, your Activites table, and your Requirements
table. If there is a relationship showing between the JC_Num and Dept_Num
of the Requirements table and the Employees table, delete it. However, you
should make sure that the Course_Num relates between the requirements table
and the Activities table.

Drag all the Activity details you care about to the grid. Also drag the
Employee Id from the Employees table and the JC_Num, Dept_Num, Course_Num,
and JC_and_Dept fields from the requirements table. You'll want to uncheck
the "show" checkbox for everything but the Adctivity Details you're
retrieving.

For convenience, drag JC_and_Dept right after your activity details, since
we're going to be doing the most work on it.

You'll notice there are a bunch of lines that have sort of gray dividers
between them and to the left you'll see that the top two of these say
Criteria and Or. We're going to use these to specify what happens with
different values of JC_and_Dept + different values of JC_Num and Dept_Num.

Under JC_and_Dept, you want to put in "False" (no quotes). So, if that is
False, then either Dept_Num or JC_Num must be equal to the value for the
employee you're looking at. To specify "either" you use different rows. So
add another "False" row for JC_and_Dept. Now, in the first row, you fill in
=Employees.JC_Num under the Jc_Num column and leave Dept_Num blank. In the
second row, fill in =Employees.Dept_Num and leave JC_Num blank.

Next, you need to say what happens if JC_and_Dept is true. So add True in
the JC_and_Dept column. In this case, you need to fill in both of the other
columns with the values that you used in different rows above, but they need
to all be in the same row here.

Now, you need to specify which employee you mean. The way you'll do this
depends on how you're calling the query. I am assuming you're just opening
it from the Queries pane and you want it to prompt you for an employee ID.
This is called a parameter query. You need to include that prompt in all
three rows, because otherwise it will limit the employee to just the one you
want in the rows the query returns that match all the other columns in that
row, but will return all employees on the other rows that match. So type in
[Enter Employee ID] in the first row under EmployeeID and then copy and
paste it down into the other two rows.

Let me know how this works out for you.

HTH;

Amy

 

[MES via AccessMonster.com]

I have another issue that maybe you can help me with that is related to what
we've been talking about here.

I need to run a report that shows what employees have not taken a course that
they are required to take. I have my tblRequirements set up as you suggested.
I also already have a query where I input a course # and get a list of all
employees that have not taken that course (all pertinent fields are included
here). Some of these employees are required to take the course, and some are
not. I need to narrow this list down to only those employees that are
required to take the course by somehow linking to my requirements table.

Any ideas?

Thanks in advance for your help.

MES

I have another question about this. I have the tblRequirements set up as
you

[quoted text clipped - 7 lines]

Any
ideas on how to do this?

Note, this is by the seat of my pants without having access to your db, so
as a first pass it may turn out to be wrong, but this is where I'd start...

Open the Query builder

Show your Employees table, your Activites table, and your Requirements
table. If there is a relationship showing between the JC_Num and Dept_Num
of the Requirements table and the Employees table, delete it. However, you
should make sure that the Course_Num relates between the requirements table
and the Activities table.

Drag all the Activity details you care about to the grid. Also drag the
Employee Id from the Employees table and the JC_Num, Dept_Num, Course_Num,
and JC_and_Dept fields from the requirements table. You'll want to uncheck
the "show" checkbox for everything but the Adctivity Details you're
retrieving.

For convenience, drag JC_and_Dept right after your activity details, since
we're going to be doing the most work on it.

You'll notice there are a bunch of lines that have sort of gray dividers
between them and to the left you'll see that the top two of these say
Criteria and Or. We're going to use these to specify what happens with
different values of JC_and_Dept + different values of JC_Num and Dept_Num.

Under JC_and_Dept, you want to put in "False" (no quotes). So, if that is
False, then either Dept_Num or JC_Num must be equal to the value for the
employee you're looking at. To specify "either" you use different rows. So
add another "False" row for JC_and_Dept. Now, in the first row, you fill in
=Employees.JC_Num under the Jc_Num column and leave Dept_Num blank. In the
second row, fill in =Employees.Dept_Num and leave JC_Num blank.

Next, you need to say what happens if JC_and_Dept is true. So add True in
the JC_and_Dept column. In this case, you need to fill in both of the other
columns with the values that you used in different rows above, but they need
to all be in the same row here.

Now, you need to specify which employee you mean. The way you'll do this
depends on how you're calling the query. I am assuming you're just opening
it from the Queries pane and you want it to prompt you for an employee ID.
This is called a parameter query. You need to include that prompt in all
three rows, because otherwise it will limit the employee to just the one you
want in the rows the query returns that match all the other columns in that
row, but will return all employees on the other rows that match. So type in
[Enter Employee ID] in the first row under EmployeeID and then copy and
paste it down into the other two rows.

Let me know how this works out for you.

HTH;

Amy

 

[Michael Gramelspacher]

I have another issue that maybe you can help me with that is related to what
we've been talking about here.

I need to run a report that shows what employees have not taken a course that
they are required to take. I have my tblRequirements set up as you suggested.
I also already have a query where I input a course # and get a list of all
employees that have not taken that course (all pertinent fields are included
here). Some of these employees are required to take the course, and some are
not. I need to narrow this list down to only those employees that are
required to take the course by somehow linking to my requirements table.

Any ideas?

Thanks in advance for your help.

MES

I have another question about this. I have the tblRequirements set up as
you

[quoted text clipped - 7 lines]

Any
ideas on how to do this?

Note, this is by the seat of my pants without having access to your db, so
as a first pass it may turn out to be wrong, but this is where I'd start...

Open the Query builder

Show your Employees table, your Activites table, and your Requirements
table. If there is a relationship showing between the JC_Num and Dept_Num
of the Requirements table and the Employees table, delete it. However, you
should make sure that the Course_Num relates between the requirements table
and the Activities table.

Drag all the Activity details you care about to the grid. Also drag the
Employee Id from the Employees table and the JC_Num, Dept_Num, Course_Num,
and JC_and_Dept fields from the requirements table. You'll want to uncheck
the "show" checkbox for everything but the Adctivity Details you're
retrieving.

For convenience, drag JC_and_Dept right after your activity details, since
we're going to be doing the most work on it.

You'll notice there are a bunch of lines that have sort of gray dividers
between them and to the left you'll see that the top two of these say
Criteria and Or. We're going to use these to specify what happens with
different values of JC_and_Dept + different values of JC_Num and Dept_Num.

Here is a design I have played with.
http://www.psci.net/gramelsp/temp/EmployeeTraining.png

If you are interested I can upload a small sample. Just tables and
queries.

 

[Amy Blankenship]

I have another issue that maybe you can help me with that is related to
what
we've been talking about here.

I need to run a report that shows what employees have not taken a course
that
they are required to take. I have my tblRequirements set up as you
suggested.
I also already have a query where I input a course # and get a list of all
employees that have not taken that course (all pertinent fields are
included
here). Some of these employees are required to take the course, and some
are
not. I need to narrow this list down to only those employees that are
required to take the course by somehow linking to my requirements table.

Take the parameter off the query I described so it returns all employees.
Then join that query to the query that shows the employees who have not
taken the course.

HTH;

Amy

 

[MES via AccessMonster.com]

Thanks - that worked.

What about if I want to query a list of employees who took a certain activity
that didn't need to?

I have another issue that maybe you can help me with that is related to
what

[quoted text clipped - 11 lines]

not. I need to narrow this list down to only those employees that are
required to take the course by somehow linking to my requirements table.

Take the parameter off the query I described so it returns all employees.
Then join that query to the query that shows the employees who have not
taken the course.

HTH;

Amy

 

[Michael Gramelspacher]

Thanks - that worked.

What about if I want to query a list of employees who took a certain activity
that didn't need to?

Presumably you have a table or query for Employee Courses Taken. You also
should have a query for Employee Required Courses. Then this might work.
Over time an employee may have had assignments to more than one department
and more than a single job code. I can imagine that some courses need to be
refreshed over time.

SELECT a.first_name,
a.last_name,
TrainingCourses.course_title,
a.completion_date
FROM [Employee Courses Taken] AS a
INNER JOIN TrainingCourses
ON a.course_nbr = TrainingCourses.course_nbr
WHERE (((EXISTS (SELECT *
FROM [Employee Required Courses] AS b
WHERE b.emp_nbr = a.emp_nbr
AND b.course_nbr = a.course_nbr)) = False));

 

[Amy Blankenship]

Thanks - that worked.

What about if I want to query a list of employees who took a certain
activity
that didn't need to?

First, we need a table that describes completion data, which is a new
requirement (originally, you just wanted to be able to tell what activities
a given employee needed to take based on JC_Num and Dept_Num.)

tblActivityComplete
CompletionID
RequirementID
EmployeeID
CompletionDate

Unfortunately, this table structure does not allow for the possibility that
an employee would take a course that he/she is not required to do, since the
relationship between the Activity and the Completion of the Activity goes
through the Requirement table. However, this is the structure I would use,
because it is the one that avoids having redundant information.

You could also do something like this:

tblActivityComplete
CompletionID
RequirementID
EmployeeID
ActivityID
CompletionDate

You could use the redundant ActivityID to allow for employees to take a
course for which there is no requirement. Then all you have to do is check
for null RequirementID's. Note that this sort of redundancy is not really
that compatible with how Access is designed to work, so if you're using
Access as your GUI you may find yourself tying yourself in knots trying to
get this to happen.

-Amy