Report with totals only, no details

[lorirobn]

Hello,

I'm creating a one-page report that will display only total counts, no
details. I want to examine the values of 2 fields on the source table,
and add to appropriate counters accordingly. MattressSize can have 3
values, Condition can have 5 values. Therefore I have 15 unbound text
boxes in the report's Footer section which have counts of the
combinations of these 2 fields.

My question is: where do I put the logic to look at detail records
and examine field contents for each row? I set up a Detail_Format
event, where I have an IF statement to add to counts accordingly, but
this is not getting executed. I'm guessing that if I don't have details
being printed, I can't use the DetailFormat event.

I am relatively new to Access programming, so feel free to give any
suggestions on the best way to do this.

Thanks in advance,
Lori

[MarcHG]

Lori
there are several more elegant approches for this.
1. a Create first a crosstab query with one of the (fixed) dimensions
as a columnname
b one of the columns mut refer to a value to aggregate (sum, count,
etc)
c The resulting columnnames will be fixed after report-creation
from that table
2. think about making a pivot-table in a spreadsheet with mattress-size
and condition as dimensions. You could easily add more dimensions at
will (brand, branch-office etc.). Every dimension is derived from a
table-column as well as the values to summarize. If the table would be
too big for a spreadsheet it could be saved as a cube or it could be
already pre-summarized though totalling the values by SQL (the
sigma-button) and then used in a spreadsheet. A query can be accessed
from a spradsheet directly.
3 try using the pivot-table form wizard to creat a pivot-table in
Access (I don't have practical experience with it yet, but it should be
probably the easiest method.)

All three give a different type of output, but should be much morea
easy and/or flexible to create.

Marc

[Lorirobn]

Hi Marc,

Thank you so much for your reply and your instructions. I followed
them, with my big Access book by my side, and created a Crosstab query
and got exactly what I wanted. I don't even think I need a pivot table
- checked it out in the book (and my users have Access 2000, so I'm not
sure if they even have this capability). Anyway, I'm fine with the
results of the Crosstab.

Just one thing, though - what do I do about the column headed "<>"? (is
this giving me the counts of non-null values?).

Thanks so much! You saved me today!
Lori

*** Sent via Developersdex http://www.developersdex.com ***

[MarcHG]

Lori,

Do you use a Access-version > 2000 ?
I don't know for sure what that column means, but I could imagine a
column for all values for the column-dimension that are not mentioned
(<> or unequal to ..) in the column-names generated by the query. The
column-names in a report are static and not derived from data-values in
the table. The column-names can change when the crosstab-query contains
new values, but the report doesn't change it's layout accordingly. I
suppose the wizard created that column in the report to count for
values not mentioned in the column-names. I don't use this feature
myself, so may be you could learn me more about it ...

Marc