conditional running sum

  • Thread starter Thread starter Patrick Caudill
  • Start date Start date
P

Patrick Caudill

Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields:
Date Adbn%
01/01/09 (Mon) 5%
01/02/09 (Tues) 6%
01/03/09 (Wed) 9%
01/04/09 (Thurs) 6%
01/05/09 (Fri) 8% I need it to end up with the following:
Date Abdn%
01/01/09 5%
01/02/09 5.5% Avg of Mon&Tues
01/03/09 6.6% Avg Mon,Tues&Wed
01/04/09 6.5% Avg Mon,Tues,Wed&Thurs
01/05/09 6.8% Avg Mon,Tues,Wed,Thurs&Fri.
This would need to reset each week. Thank you for any assistance.

EggHeadCafe - Software Developer Portal of Choice
MDI Inter-Form Communication with Events
http://www.eggheadcafe.com/tutorial...5f-20ce4f44f644/mdi-interform-communicat.aspx
 
Yes, but you would need to create a group that is based upon the week (I
would actually use something like:

DateAdd("d", -Weekday([DateField], vbMonday), [DateField])

This evaluation will give you the Sunday that starts each week. Then, in
the field you use to do your running sum, select "Over Group" as the value
for the "Running Sum" property.
 
Since the poster wants an average and not a running sum this might be a bit
more complicated.

= DAvg("[ADBN]","[SomeTable]","DateField Between #" &
DateAdd("d", -Weekday([DateField], vbMonday), [DateField]) & "# AND #" &
DateField & "#")

IF I got that expression correct this will calculate a running average of the
readings for each day of the week. But if a day is missing in the data the
average will be calculated without that day in it.

If you assume that there will always be a record for each day in the week (or
at least Monday to Friday), you can use the running sum and divide by the
weekday (adjusted).

=[ValueOfRunningSumControl] / Weekday(Date())-1

If there is a date missing in the week the Average that is calculated with the
above will be the same as if the missing day (or days) were zero.

Given
Date Adbn%
01/01/09 (Mon) 5%
01/03/09 (Wed) 9%
01/04/09 (Thurs) 6%
01/05/09 (Fri) 8%

Method one would return: 5, 7, 6.66, 7
Method two would return: 5, 4.66, 5, 5.6


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

Dale said:
Yes, but you would need to create a group that is based upon the week (I
would actually use something like:

DateAdd("d", -Weekday([DateField], vbMonday), [DateField])

This evaluation will give you the Sunday that starts each week. Then, in
the field you use to do your running sum, select "Over Group" as the value
for the "Running Sum" property.

----
HTH
Dale



Patrick Caudill said:
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields:
Date Adbn%
01/01/09 (Mon) 5%
01/02/09 (Tues) 6%
01/03/09 (Wed) 9%
01/04/09 (Thurs) 6%
01/05/09 (Fri) 8% I need it to end up with the following:
Date Abdn%
01/01/09 5%
01/02/09 5.5% Avg of Mon&Tues
01/03/09 6.6% Avg Mon,Tues&Wed
01/04/09 6.5% Avg Mon,Tues,Wed&Thurs
01/05/09 6.8% Avg Mon,Tues,Wed,Thurs&Fri.
This would need to reset each week. Thank you for any assistance.

EggHeadCafe - Software Developer Portal of Choice
MDI Inter-Form Communication with Events
http://www.eggheadcafe.com/tutorial...5f-20ce4f44f644/mdi-interform-communicat.aspx
.
Click to expand...
Click to expand...
 
John,

You may be correct. I was assuming a running sum in one field, and some
sort of a fixed divisor to give the Adbn% figure.

Dale


John Spencer said:
Since the poster wants an average and not a running sum this might be a
bit more complicated.

= DAvg("[ADBN]","[SomeTable]","DateField Between #" &
DateAdd("d", -Weekday([DateField], vbMonday), [DateField]) & "# AND #" &
DateField & "#")

IF I got that expression correct this will calculate a running average of
the readings for each day of the week. But if a day is missing in the
data the average will be calculated without that day in it.

If you assume that there will always be a record for each day in the week
(or at least Monday to Friday), you can use the running sum and divide by
the weekday (adjusted).

=[ValueOfRunningSumControl] / Weekday(Date())-1

If there is a date missing in the week the Average that is calculated with
the above will be the same as if the missing day (or days) were zero.

Given
Date Adbn%
01/01/09 (Mon) 5%
01/03/09 (Wed) 9%
01/04/09 (Thurs) 6%
01/05/09 (Fri) 8%

Method one would return: 5, 7, 6.66, 7
Method two would return: 5, 4.66, 5, 5.6


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

Dale said:
Yes, but you would need to create a group that is based upon the week (I
would actually use something like:

DateAdd("d", -Weekday([DateField], vbMonday), [DateField])

This evaluation will give you the Sunday that starts each week. Then, in
the field you use to do your running sum, select "Over Group" as the
value for the "Running Sum" property.

----
HTH
Dale



Patrick Caudill said:
Is it possible to have a conditional running sum in access. I have
found for Excel but no details for access 2007. I would like to have in
a report or query that will have a weekly grouping. I have a query that
has calculations in it and that will be by source. I have the following
sample fields:
Date Adbn%
01/01/09 (Mon) 5%
01/02/09 (Tues) 6%
01/03/09 (Wed) 9%
01/04/09 (Thurs) 6%
01/05/09 (Fri) 8% I need it to end up with the following:
Date Abdn%
01/01/09 5%
01/02/09 5.5% Avg of Mon&Tues
01/03/09 6.6% Avg Mon,Tues&Wed
01/04/09 6.5% Avg Mon,Tues,Wed&Thurs
01/05/09 6.8% Avg Mon,Tues,Wed,Thurs&Fri.
This would need to reset each week. Thank you for any assistance.

EggHeadCafe - Software Developer Portal of Choice MDI Inter-Form
Communication with Events
http://www.eggheadcafe.com/tutorial...5f-20ce4f44f644/mdi-interform-communicat.aspx
.
Click to expand...
Click to expand...
Click to expand...
 
Patrick -

Here is one way to get the data you need. Set up two queries. Change the
fieldnames to match the ones in your table. I assumed you only had data
Monday through Friday, and one row per date, but you can enhance the queries
to meet your data.

The first query will split the data into weeks for grouping:

SELECT [Table1]![abcn], Weekday([Table1]![Date]) AS DayOfWeek,
Expenditures.Expenditure, DatePart("ww",[Table1]![Date]) AS WeekNumber,
DatePart("yyyy",[Table1]![Date]) AS YearNumber
FROM [Table1];

The second query will produce a crosstab-like report with one week for each
row and 5 columns with the running averages:

SELECT Min(Query1Name.Date) AS StartOfWeek, Sum(IIf([DayOfWeek]=1,[abcn],0))
AS AvgThruMonday, Sum(IIf([DayOfWeek]<=2,[abcn],0)/2) AS AvgThruTuesday,
Sum(IIf([DayOfWeek]<=3,[abcn],0)/3) AS AvgThruWednesday,
Sum(IIf([DayOfWeek]<=4,[abcn],0)/4) AS AvgThruThursday,
Sum(IIf([DayOfWeek]<=5,[abcn],0)/5) AS AvgThruFriday
FROM Query1Name
GROUP BY Query1Name.YearNumber, Query1NameWeekNumber;

Hope that works for you. If not, post what you have entered, and what else
you need it to do.
 
Do you realize your calculations are wrong? According to my calendar, 1/4 is
a new week. You should be able to get the weekly running average with a query
using a subquery like:
SELECT tblCaudill.DateField, tblCaudill.AbdnPct,
(SELECT Avg(AbdnPct)
FROM tblCaudill clone
WHERE Format(clone.DateField,"yyyyww") =
Format(tblCaudill.DateField,"yyyyww")
AND Clone.DateField <=tblCaudill.DateField) AS RunningAvgByWeek
FROM tblCaudill;
 
The davg formula seems to be what I am looking for (from the description stated). I have tried running it with my fields. When I run it, it ends up putting [] around vbMonday. I am not sure why it would do this?



John Spencer wrote:

Since the poster wants an average and not a running sum this might be a
16-Nov-09

Since the poster wants an average and not a running sum this might be a bi
more complicated

= DAvg("[ADBN]","[SomeTable]","DateField Between #"
DateAdd("d", -Weekday([DateField], vbMonday), [DateField]) & "# AND #"
DateField & "#"

IF I got that expression correct this will calculate a running average of th
readings for each day of the week. But if a day is missing in the data th
average will be calculated without that day in it

If you assume that there will always be a record for each day in the week (o
at least Monday to Friday), you can use the running sum and divide by th
weekday (adjusted)

=[ValueOfRunningSumControl] / Weekday(Date())-

If there is a date missing in the week the Average that is calculated with th
above will be the same as if the missing day (or days) were zero

Give
Date Adbn
01/01/09 (Mon) 5
01/03/09 (Wed) 9
01/04/09 (Thurs) 6
01/05/09 (Fri) 8

Method one would return: 5, 7, 6.66,
Method two would return: 5, 4.66, 5, 5.

John Spence
Access MVP 2002-2005, 2007-200
The Hilltop Institut
University of Maryland Baltimore Count

Dale Fye wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
US Postal Service Address Validation with ASP.NET / XMLHTTP
http://www.eggheadcafe.com/tutorial...8-2e10692aec5f/us-postal-service-address.aspx
 
Because it is not recognizing vbMonday as a variable. Use 2 in place of
vbMonday - that is the value of vbMonday.

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