Field totals on a report

[Graham Daniels]

Hi all,

Using Access 2002 I have a small database used to record merit points for
pupils under different categories e.g. effort, homework, etc. The data in
these categories is held in yes/no fields in a table and appear on reports
as tick boxes

1. Is it possible to produce a report which lists totals only of each 'yes'
for each category (field) i.e. a list of pupil names showing a calculated
total of 5 homework ticks and 3 effort ticks for Joe Bloggs, 6 effort ticks
for Jane etc....

2. If so, how?

Many thanks

Graham Daniels

 

[Kelvin]

Create a query to do the grouping then base the report on the query. Your
query should group by Student and Category and count the merit points with
the criteria of Yes.

Kelvin

 

[Graham Daniels]

I have tried this but can't get the query to count the number of instances
of a YES tickbox for a given pupil.
It is counting instances within a record - which can never be more than 1
for any given record, but I need a total for a group of records all relating
to one student.

I am considering experimenting with text boxes in the group footer but how
do I set criteria here?


Thanks for your comments

Graham

 

[Kelvin]

Did you set the query to group by the student name? Then count the names
not the yes/no field. Set the criteria for the yes/no field to yes. This
way it will filter out only those names that have a yes and count how many
times that name shows up.

Kelvin

 

[Graham Daniels]

Sorry to be a pain but it's still not working as I want it to. There are 9
different YES/NO fields all, none or some of which may be ticked for all,
some or no pupils. My query at presEnt counts the number of records for all
YES values, regardless of which field was YES. I want 9 different totals for
each pupil - one total for the number of YES values in each field.
In the design of the query I now have First Of XXXX where XXXX is the field
name

I hope this makes sense.

Thanks

Graham

 

[Kelvin]

I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student then for
each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every yes to a 1
then add up all the 1's without needed a criteria at the bottom. This
should give you the correct count.

Kelvin

 

[Graham Daniels]

This has worked very well! Many thanks Kelvin

Graham

I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student then for
each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every yes to a 1
then add up all the 1's without needed a criteria at the bottom. This
should give you the correct count.

Kelvin

Sorry to be a pain but it's still not working as I want it to. There are 9
different YES/NO fields all, none or some of which may be ticked for all,
some or no pupils. My query at presEnt counts the number of records for all
YES values, regardless of which field was YES. I want 9 different totals for
each pupil - one total for the number of YES values in each field.
In the design of the query I now have First Of XXXX where XXXX is the field
name

I hope this makes sense.

Thanks

Graham


than but
how The
data

 

[Graham Daniels]

Spoke too soon!. I want the total query to work between two give dates. I
can do this no problem with other querise that don't group or have
expressions, but when I include the date field in this expression query it
insists on having some entry in the Total row. If it's group by then the
expressions don't add up the yes bvalues any more.

Graham

This has worked very well! Many thanks Kelvin

Graham

I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student then for
each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every yes to a 1
then add up all the 1's without needed a criteria at the bottom. This
should give you the correct count.

Kelvin

Sorry to be a pain but it's still not working as I want it to. There

are

9 for
all totals
for how
many

appear

on

 

[Kelvin]

For the Total row of the date set it to Where then enter your dates using
between in the criteria row. When using aggregate (grouping) queries the
Where option is used to set a criteria on the original data. If you just
put in a criteria, it is based on the result of the aggregate.

Kelvin

Spoke too soon!. I want the total query to work between two give dates. I
can do this no problem with other querise that don't group or have
expressions, but when I include the date field in this expression query it
insists on having some entry in the Total row. If it's group by then the
expressions don't add up the yes bvalues any more.

Graham

This has worked very well! Many thanks Kelvin

Graham

I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student then for
each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every yes to

a

1 are footer
but etc.
The appear

only

of

 

[Graham Daniels]

Thanks again Kelvin.
This worked.

Graham

For the Total row of the date set it to Where then enter your dates using
between in the criteria row. When using aggregate (grouping) queries the
Where option is used to set a criteria on the original data. If you just
put in a criteria, it is based on the result of the aggregate.

Kelvin

Spoke too soon!. I want the total query to work between two give dates. I
can do this no problem with other querise that don't group or have
expressions, but when I include the date field in this expression query it
insists on having some entry in the Total row. If it's group by then the
expressions don't add up the yes bvalues any more.

Graham

This has worked very well! Many thanks Kelvin

Graham



I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student

then
for

each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every yes

to

a There
are records
for count
how records
all wrote
in only

showing

a

Bloggs,

6

 

[Kelvin]

No problem.

Thanks again Kelvin.
This worked.

Graham

For the Total row of the date set it to Where then enter your dates using
between in the criteria row. When using aggregate (grouping) queries the
Where option is used to set a criteria on the original data. If you just
put in a criteria, it is based on the result of the aggregate.

Kelvin

dates.

I

query

it

insists on having some entry in the Total row. If it's group by then the
expressions don't add up the yes bvalues any more.

Graham




This has worked very well! Many thanks Kelvin

Graham



I didn't realtize you had multiple yes/no fields. In that case, the
criteria will get confused. Try this instead. Group by student then
for
each yes/no field create a field like below:

Merit1:sum(iif([Field1]=Yes, 1,0))

and set the group option to expression. This will change every

yes

to is
the count
the to
yes.

number

of be
more showing Bloggs,