Count per month of 3 priorities as well as the months total

[Dsperry101 via AccessMonster.com]

Hello ,

I have a maintenance work order database. I want to do a query for a
graph. I want to show the total work orders issued in the past months. I have
done this using the query by example screen and plotted in a chart.
Now I want to add to the query and chart. Each work order has a priority
of either "Normal" , "Unsafe to Operate" or "Production Stoppage" in the
database in the field [Priority]. I want to get the count of each category of
each [Priority] for each month. I'd like plot these values on the chart also.
I've tried several things but can't seem to get it right.
Don't know it will help but here's the SQL generated:

SELECT Format(([Work_Order_Received]),"mmm-yyyy") AS MonthYearStr, Count((
[MonthYearStr])) AS MCount, CDate(Format([Work_Order_Received],"mmm-yyyy"))
AS DateDate
FROM tblNewMwo
GROUP BY Format(([Work_Order_Received]),"mmm-yyyy"), CDate(Format(
[Work_Order_Received],"mmm-yyyy"))
ORDER BY CDate(Format([Work_Order_Received],"mmm-yyyy"));

The [Work_Order_Received] field is a string value.


Thanks ahead of time

 

[Wolfgang Kais]

Hello Danny.

Hello ,

I have a maintenance work order database. I want to do a query for
a graph. I want to show the total work orders issued in the past
months. I have done this using the query by example screen and
plotted in a chart. Now I want to add to the query and chart. Each
work order has a priority of either "Normal" , "Unsafe to Operate"
or "Production Stoppage" in the database in the field [Priority].
I want to get the count of each category of each [Priority] for each
month. I'd like plot these values on the chart also. I've tried
several things but can't seem to get it right. Don't know it will
help but here's the SQL generated:

SELECT Format(([Work_Order_Received]),"mmm-yyyy") AS MonthYearStr,
Count(([MonthYearStr])) AS MCount, CDate(Format([Work_Order_Received]
,"mmm-yyyy")) AS DateDate FROM tblNewMwo
GROUP BY Format(([Work_Order_Received]),"mmm-yyyy"), CDate(Format(
[Work_Order_Received],"mmm-yyyy"))
ORDER BY CDate(Format([Work_Order_Received],"mmm-yyyy"));

The [Work_Order_Received] field is a string value.

Thanks ahead of time

So why don't you include (for grouping) the [Priority] in your query?

 

[Dsperry101 via AccessMonster.com]

I would like to end up with the following:
MonthTotalWorkOrders MonthNormal MonthProductionStoppage
MonthUnsafeToOperate
25 15
7 3
32 20
6 6
19 10
4 5
Don't know how to do it in the query

Hello Danny.

Hello ,

[quoted text clipped - 19 lines]

Thanks ahead of time

So why don't you include (for grouping) the [Priority] in your query?

 

[Wolfgang Kais]

Hello Danny.

[quoted text clipped - 19 lines]
Thanks ahead of time

So why don't you include (for grouping) the [Priority] in your query?

I would like to end up with the following:
MonthTotalWorkOrders MonthNormal MonthProductionStoppage
MonthUnsafeToOperate
25 15
7 3
32 20
6 6
19 10
4 5
Don't know how to do it in the query

After including the Priority column, make your query a crosstab query
(use the query menu from the query design window), then select the
appropriate value in the crosstab row in the design grid:
Month = Row Heaing, Priority = Column Heading, MCount = Value.

 

[Dsperry101 via AccessMonster.com]

Wolfgang,
I'm an access novice , sorry. I can get the crosstab query selection but
then I'm lost.

Hello Danny.

[quoted text clipped - 19 lines]
Thanks ahead of time
So why don't you include (for grouping) the [Priority] in your query?

I would like to end up with the following:
MonthTotalWorkOrders MonthNormal MonthProductionStoppage

[quoted text clipped - 6 lines]

4 5
Don't know how to do it in the query

After including the Priority column, make your query a crosstab query
(use the query menu from the query design window), then select the
appropriate value in the crosstab row in the design grid:
Month = Row Heaing, Priority = Column Heading, MCount = Value.

 

[Dsperry101 via AccessMonster.com]

Wolfgang ,
OK , I figured it out , but can I get a column that has the total as
well ?

Thanks for
the assistance !!

 

[Wolfgang Kais]

Hello Danny .

Wolfgang ,
OK , I figured it out , but can I get a column that has the total
as well ?
Thanks for the assistance !!

Yes.
Add the MCount field once more to the query and use the same function
as for the value field.
In the crosstab row, select row heading for that column.
The row heading appears left to the values, but in datasheet view, you
can move that column to the end (if you like that better).