Grand total for Query Results

Topic

New Reply

I have a query that groups a table by client id, showing counts for each client id. See attached screenshot for query specs.

I would like to add another row to the results of this query that shows the total for all client ids.

How would I do this?

Thanks.

Reply | Quote

Replies

That sort of thing is best done using a report rather than a query – adding a row to a query is rather cumbersome. In this case you could probably do it with a Union query, but to get it to appear at the bottom would involve lots of tricks. Reports have the ability to sum the count of intClientType pretty easily.

Reply | Quote

You beat me to the punch Wendell
The Short Answer is You Cannot in Access using the Query Designer.
It is a feature available in SQL Server but NOT Access.

There are a couple of ways round this

1. Send the results of this query to a Tablular Form or Report
then put a SUM(countOfIntClientType) into the Report/Form Footer

2. In Order to do this in a Single Query you need to resort to a UNION Query.
And this means Writing the SQL Script
In the query window you need to use View SQL

It would, from your example look a bit like this.

However I cannot see all the correct field, calculation and table name details so this is an outline…

Code:

SELECT    YourIIF(Function) AS [WhatEverYouWantToCallIt],Count(intClientType) As [Client Types]
FROM   	tblCombinedClientetc 
WHERE 	dteDateServiced Between #StartDate# And #EndDate# 
GROUP BY YourIIF(Function)

UNION ALL

SELECT "TOTAL ALL" AS [Grand Total], Count(*) As [Total Types]
FROM   	tblCombinedClientetc 
WHERE 	dteDateServiced Between #StartDate# And #EndDate#
GROUP BY "TOTAL ALL"

NOTE in The Above, StartDate and EndDate are in Fixed date Format
and must be in the form #mm/dd/yyyy#

eg. 15th Jan 2010 would be #01/15/2010#

If you are using Parameters then the WHERELine Would be

WHERE dteDateServiced Between [Start Date] And [End Date] in both parts

Make sure you spell them the same!

You can play around with Labels after the AS and also the “Text”

Reply | Quote

Or by having a completely separate query that counts without the Group By.

Reply | Quote

Thanks for all the suggestions. I knew I could do this in a report but I wanted to try it in a query just because I wanted to try it in a query.

AndrewKKWalker, your Union suggestion worked! Thanks.

Here is the final version of the SQL:

SELECT IIf([intClientType]=1,”CCSD”,IIf([intClientType]=2,”CCSD Bags”,IIf([intClientType]=3,”HOPE”,IIf([intClientType]=4,”HOPE Bags”,”Other”)))) AS Expr1, Count([tblCombined client data].intClientType) AS CountOfintClientType
FROM [tblCombined client data]
WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY IIf([intClientType]=1,”CCSD”,IIf([intClientType]=2,”CCSD Bags”,IIf([intClientType]=3,”HOPE”,IIf([intClientType]=4,”HOPE Bags”,”Other”))))

UNION ALL SELECT “Grand Total” AS Expr1, Count([tblCombined client data].intClientType) AS CountOfintClientType
FROM [tblCombined client data]
WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]));

Attached is a screenshot of the results.

Thanks again!

Reply | Quote

You could make the headings better by changing the Expr and Automatic Access names

Code:

SELECT IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other")))) AS [Client Type], 
Count([tblCombined client data].intClientType) AS [Clients]
FROM [tblCombined client data]
WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other"))))

UNION ALL SELECT "Grand Total" AS GrandTotal, Count([tblCombined client data].intClientType) AS [Clients]
FROM [tblCombined client data]
WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]));

IF You have a numerical lookup like this you can also use Choose with IIF to make it simpler

eg


SELECT IIf([intClientType] Between 1 and 4,Choose(“CCSD”,”CCSD Bags”,”HOPE”,”HOPE Bags”,”Other”) AS [Client Type]

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

Thanks again, Andrew. I’ve tidied up the column headings.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

You could also use an additional table to hold the intClienttype and an associated description.

Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

Your query then becomes:

SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
GROUP BY ct.ClientTypeDesc

UNION ALL
SELECT “Other” AS Expr1, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

UNION ALL
SELECT “Grand Total” AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
FROM [tblCombined client data] AS cd
WHERE cd.dtDateServiced Between [Start Date] And [End Date];

I have included my test database.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote

patt,

Thanks for the reply and sample sql.

I realize that I could have done this in a better way. It was originally intended as a “quick and dirty” solution for the user to get required totals.

The four client types have not changed since I wrote the application (2+ years ago) but there is always the potential that they could change.

Thanks again.

Reply | Quote