Last Question on Select Query

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

ken

Last Question for the day. In the Query below how can I sort by the month.
i.e. Jan-Feb-Mar
instead of Feb-Jan-Mar.

Thanks for all the help
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'), Month([opendate])
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
 
Hi,


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



Hoping it may help,
Vanderghast, Access MVP
 
When I run the Query I get the following error.
The Select Statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.


Michel Walsh said:
Hi,


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



Hoping it may help,
Vanderghast, Access MVP


ken said:
Last Question for the day. In the Query below how can I sort by the month.
i.e. Jan-Feb-Mar
instead of Feb-Jan-Mar.

Thanks for all the help
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'), Month([opendate])
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
Click to expand...
Click to expand...
 
Hi,


Indeed, seems it miss a coma on my first line, right after FirstOfMonth


Vanderghast, Access MVP


ken said:
When I run the Query I get the following error.
The Select Statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.


Michel Walsh said:
Hi,


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



Hoping it may help,
Vanderghast, Access MVP


ken said:
Last Question for the day. In the Query below how can I sort by the month.
i.e. Jan-Feb-Mar
instead of Feb-Jan-Mar.

Thanks for all the help
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'), Month([opendate])
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top