Another Question about Select Queries

  • Thread starter Thread starter ken
  • Start date Start date
K

ken

Using the Query below, is it possible to also have a row at the bottom that
will
give me the totals for each column.

Thanks
Ken

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');
 
Try this UNION query

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy')
UNION
"Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls

This is 100% untested air-code.

Hope This Helps
Gerald Stanley MCSD
 
Gerald,
That appears to work (I only tested it slightly), except that you left the
word "Select" out of the second query. Modify it to this and it should work:

UNION
SELECT "Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls


--
Lynn Trapp
MS Access MVP
(e-mail address removed)
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Gerald Stanley said:
Try this UNION query

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy')
UNION
"Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls

This is 100% untested air-code.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Using the Query below, is it possible to also have a row at the bottom that
will
give me the totals for each column.

Thanks
Ken

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');


.
Click to expand...
Click to expand...
 
Lynn

Thanks for spotting that. A result of poor cut and paste.

Gerald
-----Original Message-----
Gerald,
That appears to work (I only tested it slightly), except that you left the
word "Select" out of the second query. Modify it to this and it should work:

UNION
SELECT "Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls


--
Lynn Trapp
MS Access MVP
(e-mail address removed)
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Gerald Stanley said:
Try this UNION query

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy')
UNION
"Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls

This is 100% untested air-code.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Using the Query below, is it possible to also have a row at the bottom that
will
give me the totals for each column.

Thanks
Ken

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');


.
Click to expand...
Click to expand...


.
Click to expand...
 
No problem, Gerald. We've all been there, done that.

--
Lynn Trapp
MS Access MVP
(e-mail address removed)
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Gerald Stanley said:
Lynn

Thanks for spotting that. A result of poor cut and paste.

Gerald
-----Original Message-----
Gerald,
That appears to work (I only tested it slightly), except that you left the
word "Select" out of the second query. Modify it to this and it should work:

UNION
SELECT "Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls


--
Lynn Trapp
MS Access MVP
(e-mail address removed)
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Gerald Stanley said:
Try this UNION query

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy')
UNION
"Total",
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls

This is 100% untested air-code.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Using the Query below, is it possible to also have a row
at the bottom that
will
give me the totals for each column.

Thanks
Ken

SELECT
Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');


.
Click to expand...


.
Click to expand...
Click to expand...
 
Dear Ken:

It is possible, and it is fairly easy. However, there are better ways
to get this through forms and reports.

Because you want the totals row at the bottom, you must have some way
of sorting to get this. I would add a column [BT] (Bottom Total)
which would be 0 for the current rows of your query, and 1 for the
bottom total. Then create a UNION ALL with this query and add another
SELECT query to give the totals in which BT is 1. Add an ORDER BY the
puts everything in sequence, with the BT column first.

It might look like this:

SELECT 0 AS BT, Format([opendate],' mmmm\,yyyy') AS [Month],
Count(calls.Severity1) AS CountOfSeverity1,
Count(calls.Severity2) AS CountOfSeverity2,
Sum(IIf(calls.Calltyp="Question",1,0)) AS Question,
Sum(IIf(calls.Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy')
UNION ALL
SELECT 1 AS BT, NULL, COUNT(Severity1), COUNT(Severity2),
Sum(IIf(Calltyp="Question",1,0)) AS Question,
Sum(IIf(Calltyp="Other",1,0)) AS Other,
Count(Format([opendate],' mmmm')) AS total
FROM calls
ORDER BY BT, Format([opendate],' mmmm\,yyyy');

Again, putting such detail into a query is rare and certainly
unnecessary if the data is to be displayed in a form or report. There
are much better ways to do this. However, in the spirit of ROLLUPs
and such, these kind of approaches are more and more common in
queries. In fact, we are tending here toward queries that look more
and more like the reports they feed, but only for some very complex
reasons (such as overcoming the slow speed of reports with multiple
subreports, for example.) For anything less than expert level
reasons, I'd prefer to discourage you from using this kind of feature,
but if you really need it, there it is.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top