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 (although am a programmer),
so feel free to give any suggestions on the best way to do this.

Thanks in advance,
Lori

 

[Guest]

Hi, Lori.

Assuming you have a table that has the MattressSize and Condition fields,
the way to do this is with a Totals query, where you group by the
MattressSize and Condition fields, and Count the instances of the Primary Key
field. This will give you a total count of each unique combination of size
and condition.

Your report, then, will need only three controls--the MattressSize,
Condition, and Count.

For a table named Mattress with a primary key of BedID, the SQL is:

SELECT Mattress.MattressSize, Mattress.Condition, Count(Mattress.BedID) AS
CountOfBedID
FROM Mattress
GROUP BY Mattress.MattressSize, Mattress.Condition;

To create a totals query in QBE mode, start it like a normal query, then
switch to View, Totals. Use the default Group By for designating the unique
combinations for which you want a total, then choose Sum, Count, Avg, etc.
for the fields for which you want a total. You can also specify criteria by
using Where.

Hope that helps.
Sprinks

 

[lorirobn]

Hi Sprinks,

Thank you so much for your reply! I hadn't known about the Totals
Query, but created one tonight and it works like a charm. I also then
changed it to a Crosstab query, and it's exactly what I'm looking for
(except can't figure out now to get the 'not null' column "<>" off).

Thanks again,
Lori