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

 

[Pat Hartman]

No - Relational databases are very different from spreadsheets. In a table, row number is meaningless since it is not static but depends on the current sort order.

If you want to sum a set of rows, you need to be able to use criteria to select the "set". Databases work on set theory NOT row order.

Additionally, queries support only a single level of summarization. So, unlike a spreadsheet, you can't have detail rows interspersed with totals rows. If you want to see details and totals, you need to use a report. If you want to summarize everything to the same level - by city, by department, by client, by date, whatever, you can use a query.

 

[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

 

[Pat Hartman]

You are ascribing meaning to rows that doesn't exist. Either stick with Excel or do some reading on normalization which may help you understand what we are talking about.

Also, since you have the domain (indx > 12), you can sum it in a query and don't need code.

Select Sum(yourfield) as SumYourField
From yourtable;

 

[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.

 

[Pat Hartman]

Calculations are not difficult in Access and in fact, they will usually be simpler than they were in Excel You can also do pretty much anything you want to do in Access provided you are willing and able to write code. Please, go back to using Excel for a while longer. Access is a relational database and works very differently than a spreadsheet. You need to get a better grasp of some important concepts before you continue. You are creating a nightmare for yourself. You need to take your Excel hat off and hang it in the closet when you work with Access. It is a different mindset but if you can make the transition, it will be significantly easier to work with large sets of data.

The major difference between Excel and a relational database (not just Access) is that with Excel, the presentation and data layers are merged. Row order is meaningful and every point of information has a unique way to address it (by row and column). In a database, you have rows and columns. At first blush, a table in datasheet view looks like a list in a spreadsheet. Except, in the table it is only data. There is no formatting, no row order. Row order is assigned by sorting or simply by the database engine picking up one record and then another. In the underlying spreadsheet file, adjacent columns are next to each other and one row follows another. In a table, you can address a column by its name but there is no way to refer to a specific row. You have to use the primary key to get to a specific row. In a database, we use forms and reports for presentation and they don't have to look anything like the underlying table.

1. Record IDs are not data. They have no meaning and are used ONLY to uniquely identify a record. In your query you are using the ID in a calculation. This is absolutely wrong even if it seems to work for now.
2. Relational databases work on set theory. There is no such concept as row number. Saying you want to sum the last 12 rows doesn't have any meaning unless you can identify those 12 rows some other way. You must make them a set somehow. So far you haven't been able to and I can't tell by looking at the table what the special meaning of the last 12 rows is.

So, before you can come close to actually solving your problem, you need to tell us (and yourself) what makes those last 12 rows a set.

 

[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.

 

[Pat Hartman]

I would use date() rather than Now() since Now() includes time and time isn't relevant here. I would also probably use a form to control the criteria rather than hard coding it because that will allow me to change the aggregation period. The from and through dates can default to the last day of the previous month and the first day of last month -12. Then you can change the dates if you want to change the period.

So, yes mjwakima, there is a Santa Clause

You don't need any code. You need a form with two date fields and a button to run the query (which should probably be a report rather than a query anyway but that's a different lesson).

Select fld1, fld2, Avg(fld3) as RollingAvg
From ...
Where YourDate Between Forms!yourform!txtfromdate And Forms!yourform!txtthrudate
Group by fld1, fld2;

Once you can get out of your flat, row and column view and look at your data as a set, you will find that Access is no harder than Excel and frequently easier. This case is an example. Once you build this form and query, you are done. It will work forever without modifications whereas, you would have to modify your excel formula EVERY MONTH to change the rows you are referencing and you wouldn't be able to modify your excel formula on the fly to do two years or 6 months which you could do with the form by simply overriding the default period. If you persist in trying to make Access work like Excel, you are in for a lot of coding and frustration.

 

[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.

 

[Pat Hartman]

Select fld1, fld2, Sum(cases) * 200000 / Sum(hours) as RollingAvg
From ...
Where YourDate Between Forms!yourform!txtfromdate And Forms!yourform!txtthrudate
Group by fld1, fld2;

The Where clause selects the set (domain) which is the rows you want to include in the calculation.

@David,
Domain functions should not be used in queries. Remember that for every row returned by the main query, your domain function will run a separate query. So if you have 1,000 rows in the query, you will be running an additional thousand queries. Domain functions are best used on forms or code where you are working with only one record at a time.

 

[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