Query Totals Twice In one SQL statement

[RAxel]

Here is one of my two tables' field names:

Code Gross Payroll OT Ratable Payroll Premium


What I've done is grouped them by code and have totaled the costs associated to the codes.
EX:
Code Gross Payroll OT Ratable Payroll Premium
111 $111111 $11 $111111 $111
222 $222222 $22 $222222 $222

I'm using Access 2003 and I've already done this using SQL. What i want to know is if it is possible to use that same SQL statement/query to total the totals.
EX:
Code Gross Payroll OT Ratable Payroll Premium
111 $111111 $11 $111111 $111
222 $222222 $22 $222222 $222

TOTALS $333333 $33 $333333 $333

I would like to turn this into a report in Access 2003. Thanks for any help.

 

[PHV]

Why not letting the report doing the math ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

Thanks for your speedy reply. I thought about the report but I wasn't able to find any report format that would total the columns. Do I have to manually add a field in myself (on the report design view)? Or is there one already done.

 

[RAxel]

Oops, I see the SUM option for the reports now. Sorry for the reply above this.

 

[PHV]

As we are in the Access Queries and Jet SQL forum (not the Rccess Reports one), here a pure SQL way:
SELECT Code, Sum(Payroll) AS [Gross Payroll], Sum(OT) AS [OT]
FROM yourTable GROUP BY Code
UNION ALL SELECT 'TOTALS', Sum(Payroll), Sum(OT) FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RAxel]

Thanks PHV. That code works perfectly.