Sum last 12 rows for calculation (1 Viewer)

mjwakema

Registered User.
Local time
Today, 08:24
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
 

plog

Banishment Pending
Local time
Today, 08:24
Joined
May 11, 2011
Messages
11,613
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?
 

mjwakema

Registered User.
Local time
Today, 08:24
Joined
Nov 4, 2013
Messages
11
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.
 

plog

Banishment Pending
Local time
Today, 08:24
Joined
May 11, 2011
Messages
11,613
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?
 

mjwakema

Registered User.
Local time
Today, 08:24
Joined
Nov 4, 2013
Messages
11
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

  • sample database.accdb
    600 KB · Views: 106

Users who are viewing this thread

Top Bottom