Sorry,
We have a Course table which stores all details of current courses.
We have a Staff Table which stores all staff details and finally we have the
training table which links staff to courses.
When a staff member attends a course we complete a course attended form the
information from which gets stored in the training table. Courses have
different compliancy periods between 12 months and 24 months. As we
mentioned before not all staff need to complete all the courses.
Admin staff only need to complete 4, Unqualified Staff 6 and Qualified all
10. It is also necessary to know how many Nursing staff we have. This is a
total of the unqualified and the qualified staff.
The reason I have ten queries like you said is due to the fact that there
are 10 mandatory courses. Due to the differences (compliancy periods, staff
type etc) I have created a querie to calculate how many staff are currently
compliant based on the criteria of each individual course.
These 10 queries then fuel another query which has all the totals for the 10
queries. This query is also fueled by 5 other queries which calculate how
many staff we have for each different staff type including how many nursing
staff.
All 15 queries then fuel the sub-report which is embedded in the main
Training Audit Report. The training audit report is fueled by a cross tab
query which cross references staff with courses using the date they attened
these courses as the value.
I realise that this is a very specific report and that it would be difficult
for someone in your position to make suggestions I was just wondering if
there was anything that seemed obvious. I realise having 16 queries and two
reports is alot so I thought there must be a better way.
If there is not a better way then we will stick with what we've got unless
it will seriously cause problems to our database. This report will save an
aweful lot of time before we implemented this database and it was all
calculated on paper this task would take the best part of a week!
If you need to know anything else just let me know.
Many thanks
Ian
Jeff Boyce said:
Ian
Without some idea of what the "16 queries" are doing, it will be tough to
offer specific suggestions. Something bothers me, too -- you have 10
queries to get totals for the mandatory courses, and, if I recall, you have
10 mandatory courses.
Does this mean you have one table per course?
--
More info, please ...
Jeff Boyce
<Office/Access MVP>
NoviceIan said:
Hi again,
I created a totals query like you suggested and then a report and embedded
it in the main report. The report now works exactly like i wanted it its
excellent it will save a lot of time.
However there is one thing that bothers me. In order to run this report 16
queries are run in the background in order to fuel it including a crosstab
query.
Have you any suggestions on how to make it more efficient. 10 of the
queries are gathering the totals for the mandatory courses, 4 of them are
totaling the number of different staff types and the remaining are the
combined totals query and the crosstab.
Many thanks Ian
:
Ian
I see that this is still open.
Perhaps another approach would be to create a Totals query, grouping by your
"Type" field, to get a Count of each type. You could use that to create a
small sub-report, and embed that in the main report.
More specific info about your underlying data structure (a table description
of the relevant fields and an example of data) would help in making more
specific suggestions.
--
Regards
Jeff Boyce
<Office/Access MVP>
I think you've miss understood. The combo box is NOT a lookup field, we
DO
NOT have a Compliant Y/N field your right that would be a waste of time.
We
do it exactly like you said. We are legally obliged to store the date any
staff member completed a course so in order to work out compliancy we
currently run seperate queries like you said to count the current number
of
staff who are compliant.
The report we're working on now will work out compliancy for all 10
mandatory courses, display all staff members training dates cross
referrenced
by team, pay band and course with a single click of a mouse.
Anyway getting back to the original question how can I count the qualified
and unqualified staff from the staff type field seperatly on a report. I
have managed to work out the all details for the five courses all staff
have
to do its just the courses that only qualified staff need to do that are
waiting to be finished.
If I could just find out the answer to the above question I could complete
the report.
:
Ian
A couple observations ...
If your underlying table has a combo box, you've used the "lookup" data
type -- check the tablesdbdesign newsgroup on this topic for
considerable
reasons NOT to do that.
If your underlying table has a Compliant (Y/N) field, you (your users)
will
have to maintain it. A less labor-intensive method would be to have a
query
count the number of courses and return a result if the correct number is
satisfied. Note that this approach would work for both of your
(current)
types of staff, and would work if you change the (current)
number/variety of
courses required.
--
Regards
Jeff Boyce
<Office/Access MVP>
Sorry what I'm trying to do is work out the percentage of staff
currently
compilant. This will basically be a count of dates within compliancy
range
divided by the number of staff who should attend the course and
multiplied
by
100. So basically work out a percentage for each colum.
There are 10 mandatory courses,qualified staff need to sit them all
where
as
unqualified staff only need to sit half of them. This is how we know
which
courses should be attended by which staff. So the problem I have is
that
I
need a total number of qualified and a total number of unqualified. I
can
do
it easily in a query by entering the required type into the criteria
field.
However its not that simple in reports.
The field itself in the underlyning table has a combo box to select
Qualified or Unqualified.
Ian
:
I'm not sure I understand how the count and how your crosstab query
are
related. I understood the crosstab to be completed, with course,
staff,
and
date completed.
Are you saying you want to compare each staff person with a list of
required
courses vs. actually-taken courses? The count of those folks who've
taken
all required courses would give you number completed. Subtracting
that
from
your total number of folks would give you the count of those not
completed.
How do you keep track of/document which person has to complete which
courses? Is there a time frame involved? How do you track that?
It might help to know more about your underlying data structure...
--
Regards
Jeff Boyce
<Office/Access MVP>
Hi,
I'm trying to make a training compliancy report based on a
crosstab
query.
It basically lists all the course names across the top and all the
staff
down
the lefthand side. The values are the date the staff completed
the
courses.
In the report I would like to calculate the current compliance for
the
different courses. However some staff dont need all the courses
since
we
have Qualified and Unqualified Staff.
So what I need to do is count how many qualified and unqualified
staff
we
have. This is stated in the Staff Type field but I dont know how
to
count/sum only one of the staff types in a report.
What would the expression be to count the qualified staff for
example?
I've
been trying:
=Count([Staff Type] = Qualified)
and along these lines but I'm not getting anywhere please help it
will
save
a lot of time if I can get this report working.
Ian
