Sum query provides group total as well as grand total

[surfside1]

I have a query that sums "region" "amount". The query displays:
blank region name grand total amount
region name 1 region 1 total amount
region name 2 region 2 total amount
region name 3 region 3 total amount
region name 4 region 4 total amount
region name 5 region 5 total amount
I created a report based on the query. How can I put a title for the blank region?
Thanks, surfside1

 

[lespaul]

How are you getting the grand total? A Union Query with the Grand Total in one and the grouped query in another? If that's what you've done your query probably looks something like:

SELECT Region, SUM(*) FROM TableName
UNION
SELECT '', SUM(*) FROM TableName

Just add the text:

SELECT Region, SUM(*) FROM TableName
UNION
SELECT 'Title', SUM(*) FROM TableName

If that's not how you are doing it, why don't you post your SQL, makes it a lot easier.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[surfside1]

My sql:
SELECT [tbl_2005_Spend].[COO_Region],
Sum([tbl_2005_Spend].[2005_Total_Spend])
AS SumOf2005_Total_Spend
FROM tbl_2005_Spend
GROUP BY [tbl_2005_Spend].[COO_Region]
ORDER BY Sum([tbl_2005_Spend].[2005_Total_Spend]) DESC;

The query looks great, I just want the total to have a title on the report.

Thanks for responding so quickly,
Surfside1

 

[PHV]

You may try this:
SELECT Nz([COO_Region],'Total'),
Sum([2005_Total_Spend]) AS SumOf2005_Total_Spend
FROM tbl_2005_Spend
GROUP BY [COO_Region]
ORDER BY 2 DESC

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