Calculations

[Guest]

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

 

[Jeff Boyce]

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...

 

[Guest]

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

 

[Jeff Boyce]

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

 

[Guest]

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

 

[Guest]

I had a similar problem last week; maybe this will help. The count function
provides a total count of all rcords, regardless of contents of the field.
Try comnining the IIF with Sum, along these lines

=Sum(IIF([staff type]=Qualified,1,0)) to get total of those qualified, you
might simply reverse the if(1) and else(0) replacements in this example for
your unqualified counts.

 

[Jeff Boyce]

I'll defer to JR's suggestion -- post back if that doesn't work.

Sorry I misunderstood -- I regularly get accused around here of being too
literal, and that's what I responded to (> > > The field itself in the
underlying table has a combo box to select
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

 

[Guest]

I'm not getting any luck with that, would it be easier to use the pay band
field which is a text feild. All medical staff have a letter between B & H
as their pay band. B's are unqualified D and above are qualified (we dont
have any C's).

Could we simply specify something along the lines of > C for qualified and
<C unqualified.

Ian

I had a similar problem last week; maybe this will help. The count function
provides a total count of all rcords, regardless of contents of the field.
Try comnining the IIF with Sum, along these lines

=Sum(IIF([staff type]=Qualified,1,0)) to get total of those qualified, you
might simply reverse the if(1) and else(0) replacements in this example for
your unqualified counts.

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

 

[Jeff Boyce]

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

 

[Guest]

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

 

[Jeff Boyce]

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>

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

 

[Guest]

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

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>

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

 

[Guest]

HI,

I've actually figured out the original problem so theres no longer any need
for all those queries and the 2nd report. Using the band field and the
statement

=Sum(Abs([Band] Between "I" And "D"))

That will pull out the qualified staff and then I just need to alter the
Bands I & D for unqualified and admin etc. Thanks for all your help sorry
for all the confusion.

Ian

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

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>

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