Select Query

[ken]

I am trying to generate a query against the that will give me the following
information.
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0 0 25 1 26
April 2004 2 1 35 1 39
I can get the first three columns and the total column but need help with
the Question and Other Column. All data is stored in the same table with the
following fields: ID, opendate,Sev1, Sev2 and Calltyp. Question and Other
are two items that are in the CallTyp field. This is the query I am using to
get the first three columns.
SELECT Format([opendate],' mmmm\,yyyy') AS [Month], Count(calls.Severity1)
AS CountOfSeverity1, Count(calls.Severity2) AS CountOfSeverity2,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');

Thanks for any help.
Ken

 

[Brian Camire]

You might try something like this:

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');

 

[ken]

That works Great. Thanks

You might try something like this:

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');

I am trying to generate a query against the that will give me the following
information.
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0 0 25 1 26
April 2004 2 1 35 1 39
I can get the first three columns and the total column but need help with
the Question and Other Column. All data is stored in the same table with the
following fields: ID, opendate,Sev1, Sev2 and Calltyp. Question and Other
are two items that are in the CallTyp field. This is the query I am

using

to
get the first three columns.
SELECT Format([opendate],' mmmm\,yyyy') AS [Month], Count(calls.Severity1)
AS CountOfSeverity1, Count(calls.Severity2) AS CountOfSeverity2,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');

Thanks for any help.
Ken

 

[ken]

One more Question. Is it possible to also have a row at the bottom that will
gove me the totals for each column.

Ken

You might try something like this:

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');

I am trying to generate a query against the that will give me the following
information.
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0 0 25 1 26
April 2004 2 1 35 1 39
I can get the first three columns and the total column but need help with
the Question and Other Column. All data is stored in the same table with the
following fields: ID, opendate,Sev1, Sev2 and Calltyp. Question and Other
are two items that are in the CallTyp field. This is the query I am

using

to
get the first three columns.
SELECT Format([opendate],' mmmm\,yyyy') AS [Month], Count(calls.Severity1)
AS CountOfSeverity1, Count(calls.Severity2) AS CountOfSeverity2,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');

Thanks for any help.
Ken

 

[Brian Camire]

For this you might want to use a Report based on your query. See the
"Calculate a total or average for a group of or all records in a report"
help topic for details.

One more Question. Is it possible to also have a row at the bottom that will
gove me the totals for each column.

Ken

You might try something like this:

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');

I am trying to generate a query against the that will give me the following
information.
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0 0 25 1 26
April 2004 2 1 35 1 39
I can get the first three columns and the total column but need help with
the Question and Other Column. All data is stored in the same table

with
the

following fields: ID, opendate,Sev1, Sev2 and Calltyp. Question and Other
are two items that are in the CallTyp field. This is the query I am

using

to
get the first three columns.
SELECT Format([opendate],' mmmm\,yyyy') AS [Month], Count(calls.Severity1)
AS CountOfSeverity1, Count(calls.Severity2) AS CountOfSeverity2,
Count(Format([opendate],' mmmm')) AS total
FROM calls
GROUP BY Format([opendate],' mmmm\,yyyy');

Thanks for any help.
Ken