Cumulative totals
in [Queries]
|
Prev: Averaging Hourly Data
Next: Min/Max Dates and Group By
From: SamMexico via AccessMonster.com on 18 May 2010 09:35 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: John Spencer on 18 May 2010 12:00 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 SamMexico via AccessMonster.com wrote: > 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 >
From: KARL DEWEY on 18 May 2010 15:48 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? -- Build a little, test a little. "SamMexico via AccessMonster.com" wrote: > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 > > . >
From: SamMexico via AccessMonster.com on 19 May 2010 03:53 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: John Spencer on 19 May 2010 09:12
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 > |