Calculating a Grand Total in a parameter query

[Sarah]

Hi

Can anyone tell me how I can calculate a grand total for
the records returned in a paramenter query.

I am using a form to display the records returned from
this query with a prompt box to return records relating to
specific criteria, but it would be really handy if I could
get these to display a grand total for the number of
records returned.

Thanks

Sarah

 

[Jeff Boyce]

Sarah

There's a Toolbar button that looks like a backwards "3" -- the Greek
'sigma'. This turns on/off the Totals query mode. This feature is also
available via the menu bar.

Depending on how many fields you are trying to return, you may be able to
set up a query that GroupsBy Field1, Field2, and Counts Field3 (actual
numbers may vary).

Good luck

Jeff Boyce
<Access MVP>

 

[Michel Walsh]

Hi,


Basically, you will have to make a UNION ALL query:


SELECT c, SUM(a) FROM b GROUP BY c WHERE d=parameter
UNION ALL
SELECT Null, SUM(a) FROM b WHERE d=parameter


The part before the UNION ALL supplies the group name, the field c, and the
sum of fields a for record of the groups.

The part after the UNION ALL supplies NULL to match the group name (it is
the Grand Total, so, NULL is a good group name, at least in my opinion, and
SUM(a) over all the records, since there is technically no GROUP specified,
that satisfy the where clause.


If you use MS SQL Server, you can do exactly the same with a Rollup (or a
Cube), in just one statement (without union all).


Hoping it may help,
Vanderghast, Access MVP