Union query error

[ken]

When I run the following query I get an error "You tried to execute a query
that does not include the specified expression 'Format(opendate, 'yyyy.mm.')
& '01" as part of an aggregate function. What I'm trying to get is the date
to sort by the month i.e Jan, Feb, Mar. I'm not sute what I need to do to
get the results that I need.
Thanks
Ken

SELECT
Format(opendate, 'yyyy.mm.') & '01' As FirstOfMonth,
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],'01 mmmm yyyy'),
Month([opendate])
UNION
SELECT
'3000.01.01',
"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
ORDER BY FirstOfMonth

 

[MGFoster]

When I run the following query I get an error "You tried to execute a query
that does not include the specified expression 'Format(opendate, 'yyyy.mm.')
& '01" as part of an aggregate function. What I'm trying to get is the date
to sort by the month i.e Jan, Feb, Mar. I'm not sute what I need to do to
get the results that I need.
Thanks
Ken

SELECT
Format(opendate, 'yyyy.mm.') & '01' As FirstOfMonth,
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],'01 mmmm yyyy'),
Month([opendate])
UNION
SELECT
'3000.01.01',
"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
ORDER BY FirstOfMonth

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to include non-aggregate select columns in the GROUP BY clause.
You haven't done that; therefore, the error.

If you want to sort use the column/expression in the SELECT clause or
the ordinal number of the column/expression in the SELECT clause.

To sort the months in calendar order you will have to sort by their
month numbers not their names.

Here's your query w/ my suggestions.

SELECT
Format(opendate, 'yyyy.mm.') & '01' As FirstOfMonth,
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(*) AS total
FROM calls
GROUP BY Format(opendate, 'yyyy.mm.') & '01',
Format([opendate],'01 mmmm yyyy'),

UNION

SELECT
'3000.01.01',
"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(*) AS total
FROM calls

ORDER BY Month(OpenDate)

===
Both SELECTs in this UNION query select the same thing, just that the
upper SELECT gets the OpenDate column formatted in 2 forms:
"FirstOfMonth" and "Month"; and the lower SELECT puts in artifical
constants for the "FirstOfMonth" and "Month" columns. Whatever are you
trying to do?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHrofoechKqOuFEgEQKkYACfRNam1xBvI/ahdpY/+FSpXt4fGxMAoPWA
UwAAr2r4vAlpsinknrwH1FmQ
=3NEM
-----END PGP SIGNATURE-----

 

[[MVP] S.Clark]

Try:

SELECT
Format(opendate, 'yyyy.mm.') & '01' As FirstOfMonth,
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, 'yyyy.mm.') & '01',
Format([opendate],' mmmm yyyy')

Do this on it's own. When settled, add the rest of the union stuff in.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Michel Walsh]

Hi,

try:

SELECT
Format(opendate, 'yyyy.mm.') & '01' As FirstOfMonth,
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, 'yyyy.mm.') & '01',
Format([opendate],'mmmm yyyy'),
Month([opendate])

UNION ALL ...




Hoping it may help,
Vanderghast, Access MVP