Last Question on Select Query

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
 
M

Michel Walsh

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
 
K

ken

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...
 
M

Michel Walsh

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query error 3
Another Question about Select Queries 5
Totals query rework 1
Select Query 4
Need selective count within grouping, how? 1
Query on Duration 1
Running sum expression 2
crosstab problem 2

Top