Cumulative totals

[SamMexico via AccessMonster.com]

Hi everyone, I would like to create cumulative totals per month to create a
chart. I have got this far:

SELECT Data.Region, Data.[Month Year], Count(Data.[Month Year]) AS
[CountOfMonth Year]
FROM Data
GROUP BY Data.Region, Data.[Month Year]
HAVING (((Data.Region)="Leicester" Or (Data.Region)="Nottingham" Or (Data.
Region)="Northampton" Or (Data.Region)="Kettering" Or (Data.Region)="Derby")
AND ((Data.[Month Year]) Is Not Null));

...but I really have no idea about how to add the values together for both
Leciester and Nottingham on a monthly basis...as the query needs to produce
both the individual region values and the combined values but they are
separated by region.

Any ideas would be gratefully appreciated

Sam

 

[John Spencer]

It is easiest to do this in a report and use a report group to get the monthly
totals.

You can get the totals into a query by using a UNION query.

SELECT Data.Region, Data.[Month Year], Count(Data.[Month Year]) AS
[CountOfMonth Year]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY Data.Region, Data.[Month Year]
UNION
SELECT "Total All Regions", Data.[Month Year], Count(Data.[Month Year]) AS
[CountOfMonth Year]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY "Total All Regions", Data.[Month Year]
ORDER BY [Month Year], Region

This gives you totals for the month. It does not give you cumulative totals.
If that is what you want then post back. Someone (other than me) will
probably be able to help you get a cumulative total.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Your Data.[Month Year] seems like a text field and therefore it will need a
lot of massaging to be able to have cumulative totals per month.

Do you have a DateTime field available?

 

[SamMexico via AccessMonster.com]

Hi Guys, thanks for your responses

I do have a Date/time field as well which is 'Date of Consent' - I set up the
'Month Year' field as I only wanted to report the month...if the cumulative
totals can be calculated through that I'd be very interested to know how?

Thanks,

Sam

 

[John Spencer]

Going back to your original query

SELECT Data.Region, Format([Date of Consent],"mmm yyyy") as MonthYear,
Count([Date of Consent]) AS [CountMonthYear]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY Data.Region, Format([Date of Consent],"mmm yyyy")

Another option
SELECT Data.Region
, Year([Date of Consent]) as TheYear
, Month([Date of Consent]) as TheMonth
, Count([Date of Consent]) AS [CountMonthYear]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY Data.Region
, Year([Date of Consent])
, Month([Date of Consent])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this for both monthly and cumulative --
SELECT Format([Date of Consent],"mmm yyyy") AS Month_Year, Data.Region,
Count(Data.[Date of Consent]) AS CountMonthYear, (SELECT Count([XX].[Date of
Consent]) FROM Data AS [XX] WHERE Format([XX].[Date of Consent],"yyyymm")
<=Format([Data].[Date of Consent],"yyyymm")) AS Cumulative
FROM Data
WHERE (((Data.Region) In
("Leicester","Nottingham","Northampton","Kettering","Derby")))
GROUP BY Format([Date of Consent],"mmm yyyy"), Data.Region, Format([Date of
Consent],"yyyymm")
ORDER BY Format([Date of Consent],"yyyymm");

--
Build a little, test a little.

Going back to your original query

SELECT Data.Region, Format([Date of Consent],"mmm yyyy") as MonthYear,
Count([Date of Consent]) AS [CountMonthYear]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY Data.Region, Format([Date of Consent],"mmm yyyy")

Another option
SELECT Data.Region
, Year([Date of Consent]) as TheYear
, Month([Date of Consent]) as TheMonth
, Count([Date of Consent]) AS [CountMonthYear]
FROM Data
WHERE Data.Region IN ("Leicester","Nottingham","Northampton","Kettering","Derby")
AND Data.[Month Year] Is Not Null
GROUP BY Data.Region
, Year([Date of Consent])
, Month([Date of Consent])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Guys, thanks for your responses

I do have a Date/time field as well which is 'Date of Consent' - I set up the
'Month Year' field as I only wanted to report the month...if the cumulative
totals can be calculated through that I'd be very interested to know how?

Thanks,

Sam

.

 

[SamMexico via AccessMonster.com]

Hi Karl,

Thanks for the code - when I tried it I got this error message 'You tried to
execute a query that does not include the specified expression ‘Format([Date
of Consent],â€yyyymmâ€)’ as part of the aggregate function.'

Any ideas?

Thanks,

Sam

 

[SamMexico via AccessMonster.com]

Sorry about that - just got it to work...

Thank you both for all your help!

Sam

 

[SamMexico via AccessMonster.com]

Hi guys, I spoke too soon...

I now have a problem creating the graph from the code Karl sent, I get the
error 'The Micorsoft Jet database engine does not recognise '[Data].[Date of
Consent]' as a valid field name or expression'.

Which is a bummer as the query is perfect otherwise...

Any help would be great

Sam