Query total by month (1 Viewer)

[foshizzle]

I have a query that I need to total by each month of the year. Field names and types are as follows

ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)


Now, I need to just get the total of each of these fields per month for the entire year. So it would look like

Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...

Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..

SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

 

[foshizzle]

Also, I tried to make a crosstab query based off this query but it would only let me choose 3 columns...

 

[jdraw]

What exactly does this mean?
RampCheckTrue (1 or 0)

Is RampCheckTrue a Boolean Yes/No data type?

 

[foshizzle]

Yes, It was a checkbox field but couldnt total it. So I have it converted to 1 for true and 0 for false

 

[foshizzle]

ideas anyone?

 

[JHB]

Post some sample data in an Excel sheet, or in a database.

 

[namliam]

The distinct rows most likely come from the fact that you have the ID in the select.

SELECT [COLOR="red"]DISTINCTROW tblBlotter.ID,[/COLOR] Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY [COLOR="Red"]tblBlotter.ID,[/COLOR] Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

Try removing the red bits and if that helps you along.

 

[foshizzle]

Here is a sample DB of the table and query. The excel document has the formula and data I am trying to match.

So the totals of each type of Response/Check for each month.
Then the Monthly % Change, which is I'm guessing would be written something like
((CurrentMonth-PreviousMonth)/PreviousMonth*100). Except somehow these months need to be written statically I believe - so each month that advances, I could retain previous months data.

 

[foshizzle]

Just saw the response from namliam. Let me look at this and check the results..

 

[foshizzle]

Looks good.. Thanks.
How do you suppose I can get the final calculation I need

So the totals of each type of Response/Check for each month.
Then the Monthly % Change, which is I'm guessing would be written something like
((CurrentMonth-PreviousMonth)/PreviousMonth*100). Except somehow these months need to be written statically I believe - so each month that advances, I could retain previous months data.

 

[namliam]

You can do this in 2 queries:
1) Save this as Quer1

SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
GROUP BY Year([tblBlotter].[EntryDate]), Month([tblBlotter].[EntryDate]), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])), Month(DateAdd("m",-1,[tblBlotter].[EntryDate]));

2) Save this as Query2

SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter
GROUP BY Year([tblBlotter].[EntryDate]), Month([tblBlotter].[EntryDate]), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])), Month(DateAdd("m",-1,[tblBlotter].[EntryDate]));

NOTICE it is EXACTLY the same

3) make a 3rd query:

SELECT Query1.*, Query2.*
FROM Query1 LEFT JOIN Query2 ON (Query1.PrevMonth = Query2.Month) AND (Query1.PrevYear = Query2.Year);

You can make any calculation you like

 

[foshizzle]

The Grouping/Counts are great! Thanks!
My formula could stand a bit of help if you can though.
It keeps prompting me to enter the values when I run the report.

Expr1: (([Month]-[qryBlotter1]![PrevMonth])/[qryBlotter1]![PrevMonth]*100)

 

[spikepl]

When I count yes-responses for a Boolean then instead of

Sum(IIf([CKPoliceResponse],1,0))

I use

-Sum([CKPoliceResponse])

 

[namliam]

why the Flieping fliep would you calculate with the actual months, that doesnt may ANY sence what so ever.

 

[foshizzle]

lol. Sorry thats not what I meant. The months calculate correctly.
I just wanted to add one more expression at the end of this query to complete it.

So in effect, if this is Month2, I need
MonthlyChange=((Month2-Month1)/Month1*100)
and
MonthlyChange=((Month3-Month2)/Month2*100)

Always... No matter what the current month is.

I'm just not sure where to begin, given I now have 3 queries :/

 

[namliam]

Well what values do you have in a single line? The two individual months and their values

So with each of the lines in the 3rd query you can do what ever calculation you want between the two months.

 

[namliam]

Futhermore your database you linked in your other thread...
http://www.access-programmers.co.uk/forums/showthread.php?t=260648

you didnt build the KEY solution of the third query...

SELECT qryBlotter1.*, qryBlotter2.*
FROM qryBlotter1 LEFT JOIN qryBlotter2 ON (qryBlotter1.PrevMonth = qryBlotter2.Month) AND (qryBlotter1.PrevYear = qryBlotter2.Year);

Perhaps if you do, you may be able to see the solution should be a lot easier than you seem to believe....

 

[foshizzle]

Oops.
I did, just forgot I had another query there bc I had it named "qryBlotterTotals"
(My naming conventions are weak) This is the one you are referring to.

SELECT qryBlotter1.*, qryBlotter2.*
FROM qryBlotter1 LEFT JOIN qryBlotter2 ON (qryBlotter1.PrevYear = qryBlotter2.Year) AND (qryBlotter1.PrevMonth = qryBlotter2.Month);

 

[foshizzle]

So given this, I think the query should be something like

(([qryBlotter1]![Month] - [qryBlotter1]![PrevMonth] )/ [qryBlotter1]![PrevMonth] *100)

But thats not right. And further, I just dont get how I would use both the year and month to be sure the correct information correlates..

 

[namliam]

You dont calculate using the months, you calculate using the fields you want to know the difference between....

The queries I gave you already
1) calculate the previous month
2) correlate the information between the two...
using the join in the last query: (qryBlotter1.PrevYear = qryBlotter2.Year) AND (qryBlotter1.PrevMonth = qryBlotter2.Month)