Calculating Cumulative Totals

[duluter]

Hi, everyone.

I need to create a summary report and I'm having trouble constructing the query. Here is a representation of my data table:

[U]FARM      WORKORDER   SURVEYDATE     PESTCOUNT[/U]
MyFarm     2009001       6/12/2009          10
MyFarm     2009002       6/15/2009          5
MyFarm     2009002       6/20/2009         50
TestFarm   2009003       6/10/2009          20
TestFarm   2009003       6/17/2009          100
TestFarm   2009003       6/24/2009          160
TestFarm   2009003       6/30/2009          200

And here is what I want my summary report to look like:

[U]Farm      WorkOrder   LastSurveyDate  LastPestCount   CumulativeCount[/U]
MyFarm     2009001       6/12/2009           10                10
MyFarm     2009002       6/20/2009           50               255
TestFarm   2009003       6/30/2009           200              3040

Thus, each work order is reduced to one line. Displayed is the date of the most recent survey and the pest count observed on that survey. Also displayed is the "cumulative pest count", which is a running calculation of the number of pests observed at that site.

The cumulative total is sort of an interpolated total. The way the cumulative total formula works is this:

[U]Date          Count[/U]
1/1/2009      1
1/7/2009      20
1/10/2009    75

The number of days between the first survey date and the second date is six (1/7/2009 - 1/1/2009). 6 * 20 = 120. So the cumulative total after the second survey is 121, which is 120 plus the prior cumulative total. After the third survey date, the cumulative total is 121 + (3 days * 75 pests) = 346. So 346 would be displayed in the report, along with the 1/10/2009 survey date and the last pest count of 75.

Is this making sense? If not, let me know and I can rephrase.

I'm pretty lost about where to begin on this. Any help would be greatly appreciated.


Thanks,

Duluter

 

[DCrake]

In theory Yes it can be done. Not easy but acheivable. Your main problem will be the cummulative totals. To get the first 4 items you need a select query

SELECT Farm, WorkOrder, Last(SurveyDate) As LastSurveyDate, Last(PestCount) As LastPestCount From TableName Group By Farm, WorkOrder;

Then you will need a function to calculate the Cummulative totals

The number of days between the first survey date and the second date is six (1/7/2009 - 1/1/2009). 6 * 20 = 120. So the cumulative total after the second survey is 121, which is 120 plus the prior cumulative total. After the third survey date, the cumulative total is 121 + (3 days * 75 pests) = 346. So 346 would be displayed in the report, along with the 1/10/2009 survey date and the last pest count of 75.

You will need to pass the WorkOrder number to the function

Your function will then get the first and last survery dates for that works order and calculate the DateDiff in Days. Then multiply by 20

Other actions will need to take place to get the remaineder of the calulation but you can see where I am coming from. You also need to consider what happens if there is only one survey date for that order.

Once tested and agreed your query will look like this

SELECT Farm, WorkOrder, Last(SurveyDate) As LastSurveyDate, Last(PestCount) As LastPestCount, CummulativeTotals: [COLOR="Red"]GetTotals(WorkOrder) [/COLOR] From TableName Group By Farm, WorkOrder;

Edit:
Looking further at your totals figure it may be better outputting your data to Excel and let Excel do the running total as this is more inclinded to Excel than Access (only becuase it uses previous rows).

David

 

[duluter]

David:

Your suggested solution is very helpful. Basically we're simplifying the query down to a couple of Last functions and a Group By clause to get the last record for each work order. Then we're outsourcing the heavy lifting of calculating the cumulative total to a VBA routine. This makes sense to me. It seems like the function can be written in VBA almost as easily as getting it all working in Excel, so I think I'll give that route a try.

Thanks very much.

Duluter