Cumulative totals
in [Queries]
|
Prev: Averaging Hourly Data
Next: Min/Max Dates and Group By
From: KARL DEWEY on 19 May 2010 11:04 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. "John Spencer" wrote: > 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 > > SamMexico via AccessMonster.com wrote: > > 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 > > > . >
From: SamMexico via AccessMonster.com on 24 May 2010 05:44 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: SamMexico via AccessMonster.com on 24 May 2010 05:50 Sorry about that - just got it to work... Thank you both for all your help! Sam -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: SamMexico via AccessMonster.com on 24 May 2010 05:59
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 |