Summary total report

[memilanuk]

I must be doing something completely wrong; it doesn't seem like this should be this hard to accomplish. Then again, it is 0400-ish here and it's been a looong night shift

What I have: a query that displays a number of fields depending on certain criteria. Specifically if a person is a 'Regular' member and not expired, or if they are a 'Life' member, and then a number of fields with other pertinent information, including Yes/No fields for what kind of membership they have (Regular, Life, LEO, Jr.) and where they purchased their membership at.

What I want/need is to create a summary report for taking to the monthly volunteer board meetings showing we have X number of Regular members, Y number of Life members, Z number of LEO, etc. as well as being able to show a basic breakdown of where people are buying their memberships at: i.e. how many at store 'A', how many at store 'B', how many purchase directly at the club, etc.

So far I've been going round and round in circles trying to figure out if I can make this work from the query/tables I have, or if I need to create a Totals query. I got the results (more or less) I wanted by creating a single totals query for one value (Regular member) - maybe I'm missing something but there's got to be a simpler way than creating a separate totals query for each thing? The 'Queries' section of my Navigation Pane is growing by leaps and bounds; I didn't want to have to add four or five more, one for each aspect if I could avoid it. Can I?

Thanks,

Monte

 

[Galaxiom]

Yes/No fields can be counted very easily because No is store as zero and Yes as minus one.

Use textboxes with control sources set as:
=Abs(Sum([fieldname]))

Fieldnames are from the Record Source query of the Report.

 

[Galaxiom]

If the membership can only be one type then it would be better to use MemberType as a single field and store a code to represent that type. This allows an unlimited number of types so more can be added without changing the table design.

To count the MemberType field use a Domain Count as the Control Source of a textbox.

=DCount("*", "table/queryname", "MemberType=1")
Change the ID number as requred.

 

[memilanuk]

Unfortunately the member types are not all mutually exclusive - Regular and Life are, but some of the others are not a person can be both a LEO and a Life member, or Junior and Regular, etc. At this point I'm working with the 'quick-n-dirty' version; I'll have to sort out table structure and deep thought and all that at a later date.

I'd seen the various 'Domain' options like DCount... guess I'm a little fuzzy as to what exactly they are referring to as a 'domain'?

TIA,

Monte

 

[Galaxiom]

No problem with the member types overlapping. You already have the right structure with the Boolean fields.

Basically the domain is the table or query as in this Domain Count.
=DCount("*", "table/queryname", "MemberType=1")

But your yes/no fields can use this very efficient expression as I suggested in my first post:
=Abs(Sum([fieldname]))

Just make a textbox and type that into it in design mode.
The Abs converts the negative to positive while the Sum totals the values in the field. Each Yes is -1 so the positive value of the total is the number of yes records. This is an alternative that does the same thing:

=0-(Sum([fieldname]))

 

[memilanuk]

Ah. That explanation makes the whole 'Domain' thing make a wee bit more sense.

I was mucking about trying to hammer a larger report into shape - one that lists every member, and displays the checkboxes for the status of each one for each yes/no field - makes things very 'visual' and easy for the groundskeeper to check the list and get a quick idea of who is and isn't a current member. Towards the end I started playing with the 'Totals' button on the 'Grouping & Totals' section of the Ribbon... and when I selected 'Count Values' I took a peek at the Control Source expression it gave me...

=-Sum([Regular])

So essentially the same thing you had as =Abs(Sum([Regular]). Now I have a better idea of how to insert the appropriate totals where I need them.

Thanks!

Monte

 

[Galaxiom]

Incidentally if you just want to count all the records use:
=Count(*)

If you specify a field Access tests each value for Null.
Consequently it does not return the count of all the records unless every record has a value in the particular field.
Moreover the testing for Null takes more time.

 

[memilanuk]

Yep, got that one. I do have it counting all the records in one field (Member Since) so that I have totals for how many Regular Members, how many Life Members, etc. and finally how many Members overall (since any combination of the above won't necessarily match the overal total).

If you don't mind... this is a little bit off the particular subject of summary/totals, but it is still involving the reports with the Yes/No fields... is there a simple/easy way to get the checkboxes centered in their columns? By default they seem to all go in the upper left corner of their control. I'm not sure if thats the right terminology; when I go into Layout mode and click on one of the checkboxes it shows a sizeable orange outline that matches the width of the column. Inside that outline, the checkbox always gets stuck in the upper left corner and I haven't been able to find a way to make it center horizontally (and vertically) inside the outlined area.

Thanks,

Monte