Sum last 12 rows for calculation

[mjwakema]

Good Afternoon,

I am a new to Access. I was hoping someone would be kind enough to help me with an issue I am facing. I have a table with 4 columns: ID (1,2,3,4,...),Cases, Hours, RIR. In the RIR column I would like to use the following formula.

I would like to use a query to Sum the last 12 rows (after index row 12 of course )

Is this possible in Access?

In excel, I would know how to do this, but I really would like to learn how to do it in Access 2010.

Thank you

 

[plog]

In database tables, data has no order. There is no first, no last, no next, no prior. Order only exists when you tell it to exist. So you can't 'sum the last 12 rows of your table' without defining what the last 12 rows are.

What field do you want to use to order your table by for this? ID? Can you provide some sample data from your table and the result you want (hint: include at least 13 rows of data so I can determine which one should be omitted).

 

[mjwakema]

I solved this
Here is the funciton I wrote and then put it in a query.
Function sumHoursAppleton(INDx) As String
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LGST As String
'Dim INDx As Integer
Dim Sum As Double
Dim mySum As Double
Dim myTotal As Integer
'Open connection to current Access database
Set db = CurrentDb()
'Create SQL statement to retrieve value from GST table
LSQL = "select ID from tblAppleton"
Set Lrs = db.OpenRecordset(LSQL)
Sum = 0
mySum = 0

'sum the last 12 rows of data
If INDx > 11 Then
For myTotal = 1 To 12
LGST = DLookup("[hours]", "tblAppleton", "[ID] = " & INDx)
Sum = CDbl(LGST)
mySum = mySum + Sum
INDx = INDx - 1
Next myTotal
End If
Lrs.Close
Set Lrs = Nothing

sumHoursAppleton(INDx) = mySum
End Function

 

[mjwakema]

Pat,
I know that I am new to this, but I believe that what I want to do is not impossible. I do agree with you that the sort order of my database is important. This database will be hidden and always sorted by ID. The reason I needed to sum the numbers was so that I could calculate my RIR. I have attached a sample database so that you could see what I did. It seems to be working fine.

Coming from the land of Excel, I wanted to be able to learn and use Access. It does have some features (queries, reports) that make some of what I am doing much easier than Excel. That said I still need to be able to do calculations. Seems strange to me that this is so dificult in Access

I also cleaned up my code to make it easier to understand. It was a mess before.

 

[mjwakema]

Thanks for the reply, your right I must not understand Access and I am just surprised that it is so very dificult to do simple calculations.

The reason I wanted to sum the last 12 data points was because this is a 12 month rolling average. I apoligize as I did not attacth the date table. The 1 table I showed you was one of many different places that is being tracked. I was just trying to keep it simple.

As for using the ID in a calculation, I was only using the ID to find the place in the recordset for the data I needed (Cases and hours). I thought that was what it was for. Is this not why there is a primary key so one can locate data. I used it to find the hours column at a specific loaction in the recordset.

It is a shame really that I may end up doing this project in Excel as Access has many benefits. It just cannot do the calculations that I require.

 

[David R]

The reason I wanted to sum the last 12 data points was because this is a 12 month rolling average.

Aha, now we're getting somewhere.

In Access all you have to do here (assuming you've stored the month/date somewhere in the row) is tell it the date range you want. Between Now() AND Now()-365, etc...however you defined it.

The good news is it's no longer about a specific # of rows, so now Access can deal with it.

 

[mjwakema]

Thank you. You are correct and I do still think in terms of rows and columns.

I am still confused on summing the months. I can see that I can sum each month individually, but I need a sum of the hours for the last 12 months. I did go through a video training program on Access, but I do not remember seeing anywhere where you can sum 12 months of data unless you are doing a crosstab query.

Confused but hopefully making progress.

Thank you very much for you guidance

 

[mjwakema]

also, please remember the reason that I want to sum the last 12 months is so that I can use this number in another formula.
Formula is: 12 month total for [cases] *200000/12month total [hours]

Thanks

 

[David R]

Look into the DSum() function.

 

[mjwakema]

Pat,

Sorry for being so thick

I am taking it you mean that fld1 = cases, fld 2=hours and the where clause goes in the report date. If I do this and run the query even without the formula, I get a empty result.

attached example