Sum last 12 rows for calculation

mjwakema

Registered User.
Local time
Today, 11:01
Joined
Nov 4, 2013
Messages
11
Good Afternoon,

I am a new to Access, but have programmed in Excel. I was hoping someone would be kind enough to help me with an issue I am facing. I have a table with 3 columns: Cases, Hours, RIR. In the RIR column I would like to use the following formula.
Sum the last 12 rows in the cases column *200000 / sum of the last 12 rows in the hours column.
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
 
This sounds more like a job for a query than a macro. However, there's one thing you have to learn when moving from Excel to Access: data has no order until you explicitly give it order by using the ORDER BY clause in a query.

That means there is inherently no first record, no last record, no 12th record, no next and no prior record. So to achieve what you want, you have to have a field in your data on which you can apply an order. What field is that?
 
Thanks for the reply. I do have a Primary key column that is numbered. It starts at 1. The ID column is also a foreign key to the Year/Month table which is auto numbered. It is in a 1 to 1 relationship. Could I use this column to order my data.

Note: the Year/Month table is the Master table. If a record is deleted in this table that is propigated to all the tables and if a record is added then it adds a record to all tables.
 
That's good, just making sure you had a field that you could order on. Could you post some sample data from your table? And also what the exact results you want based on that sample data?
 
Here is a sample database with some made up numbers, but it represents what I would like to do. In the RIR column, I would like the calculation that I stated in an early post. I was thinking that I would probably have to wrap it in a if/then statement as the 1st 11 recordes would not have enough data to do a 12 month moving sum
 

Attachments

Users who are viewing this thread

Back
Top Bottom