problem with counting yes/no fields in a report

[Guest]

Sorry this may be lengthy to get out the question.

I have a database that tracks birthdays and anniversaries for employees from
many different sections in the company. I have set up the table and form
that the report is based on with yes/no fields for birthday, anniversary, and
each of 20+ sections that are in the company.

When I make the report I have a header for date tracked by month, birthday,
and anniversary. I use =Sum(IIf([birthday],1,0)) and the same for
anniversary to count the total number of each within the date header and that
part works as expected.

The problem is when I try and show the number of each of those under the
section checked for each record. I use =Sum(IIf([sectiona],1,0)) to count
those records and I end up with two sets of birthday and anniversary listings
under each date group. One shows birthday and the correct numbers for that
date group and for each section and 0 anniversary then another set of
birthday showing 0 and anniversary showing correct numbers for that and the
section.

I am not sure if that is clear so here is what it looks like.

Birthdays and Anniversaries

SectionA SectionB
SectionC SectionD
Jan

Birthday 2 1 0
0 1
Anniversary 0
Birthday 0 0 0
2 0
Anniversary 2

Feb

Birthday 3 0 2
1 0
Anniversary 0
Birthday 0 1 1
0 1
Anniversary 3

Total birthday 5
Total anniversary 5

I want to get birthdays and anniversaries to show together in the same date
group without the duplicate headings and still show the sections correctly.
If I do not use a separate birthday and anniversary header I can still come
up with the correct numbers for birthday and anniversary but the sections
will group both those together and will count either a birthday or
anniversary and give a combined number.
Any help will be greatly appreciated thank you.

 

[Wayne Morgan]

Could you leave them grouped separately but hide them? Then, in the section
where you want them shown together, set the control source of the
textbox(es) in that section to the value of the textboxes in the individual
sections (i.e. =[txtOtherTextbox])

 

[Guest]

When I try that if there is both a birthday and anniversary in sectiona for
the month of Jan I will get

sectiona
Birthday 1 2
anniversary 1

I am looking for a way to get

sectiona
birthday 1 1
anniversary 1 1

instead of the way it shows now as the original post indicated.

Could you leave them grouped separately but hide them? Then, in the section
where you want them shown together, set the control source of the
textbox(es) in that section to the value of the textboxes in the individual
sections (i.e. =[txtOtherTextbox])

--
Wayne Morgan
MS Access MVP

Sorry this may be lengthy to get out the question.

I have a database that tracks birthdays and anniversaries for employees
from
many different sections in the company. I have set up the table and form
that the report is based on with yes/no fields for birthday, anniversary,
and
each of 20+ sections that are in the company.

When I make the report I have a header for date tracked by month,
birthday,
and anniversary. I use =Sum(IIf([birthday],1,0)) and the same for
anniversary to count the total number of each within the date header and
that
part works as expected.

The problem is when I try and show the number of each of those under the
section checked for each record. I use =Sum(IIf([sectiona],1,0)) to count
those records and I end up with two sets of birthday and anniversary
listings
under each date group. One shows birthday and the correct numbers for
that
date group and for each section and 0 anniversary then another set of
birthday showing 0 and anniversary showing correct numbers for that and
the
section.

I am not sure if that is clear so here is what it looks like.

Birthdays and Anniversaries

SectionA SectionB
SectionC SectionD
Jan

Birthday 2 1 0
0 1
Anniversary 0
Birthday 0 0 0
2 0
Anniversary 2

Feb

Birthday 3 0 2
1 0
Anniversary 0
Birthday 0 1 1
0 1
Anniversary 3

Total birthday 5
Total anniversary 5

I want to get birthdays and anniversaries to show together in the same
date
group without the duplicate headings and still show the sections
correctly.
If I do not use a separate birthday and anniversary header I can still
come
up with the correct numbers for birthday and anniversary but the sections
will group both those together and will count either a birthday or
anniversary and give a combined number.
Any help will be greatly appreciated thank you.

 

[Wayne Morgan]

If you are counting 2 items, birthdays and anniversaries, then you'll need
to refer to each count in the textbox mentioned or create a textbox for each
count.

Example:
One textbox
="Birthdays: " & [txtBirthdaysCount] & " Anniversaries: " &
[txtAnniversariesCount]

There would be no counting done in the SectionA textbox, just a compilation
of the counting you did earlier.

--
Wayne Morgan
MS Access MVP

When I try that if there is both a birthday and anniversary in sectiona
for
the month of Jan I will get

sectiona
Birthday 1 2
anniversary 1

I am looking for a way to get

sectiona
birthday 1 1
anniversary 1 1

instead of the way it shows now as the original post indicated.

Could you leave them grouped separately but hide them? Then, in the
section
where you want them shown together, set the control source of the
textbox(es) in that section to the value of the textboxes in the
individual
sections (i.e. =[txtOtherTextbox])

--
Wayne Morgan
MS Access MVP

Sorry this may be lengthy to get out the question.

I have a database that tracks birthdays and anniversaries for employees
from
many different sections in the company. I have set up the table and
form
that the report is based on with yes/no fields for birthday,
anniversary,
and
each of 20+ sections that are in the company.

When I make the report I have a header for date tracked by month,
birthday,
and anniversary. I use =Sum(IIf([birthday],1,0)) and the same for
anniversary to count the total number of each within the date header
and
that
part works as expected.

The problem is when I try and show the number of each of those under
the
section checked for each record. I use =Sum(IIf([sectiona],1,0)) to
count
those records and I end up with two sets of birthday and anniversary
listings
under each date group. One shows birthday and the correct numbers for
that
date group and for each section and 0 anniversary then another set of
birthday showing 0 and anniversary showing correct numbers for that and
the
section.

I am not sure if that is clear so here is what it looks like.

Birthdays and Anniversaries

SectionA SectionB
SectionC SectionD
Jan

Birthday 2 1 0
0 1
Anniversary 0
Birthday 0 0 0
2 0
Anniversary 2

Feb

Birthday 3 0 2
1 0
Anniversary 0
Birthday 0 1 1
0 1
Anniversary 3

Total birthday 5
Total anniversary 5

I want to get birthdays and anniversaries to show together in the same
date
group without the duplicate headings and still show the sections
correctly.
If I do not use a separate birthday and anniversary header I can still
come
up with the correct numbers for birthday and anniversary but the
sections
will group both those together and will count either a birthday or
anniversary and give a combined number.
Any help will be greatly appreciated thank you.