Referencing previous data in a query.

[ShaunMBrown]

Hello,

I'm fairly new to Access and I'm running into a problem with a query. I don't know if I'm approaching this incorrectly or if this is even possible but I've been searching through the posts and haven't found an answer.

The problem is this: I'm trying to run a query that has three columns (well in actuality, many more but the problem boils down to these three); An AutoNumber column, A Percentage column, and A Total column. What I want is for the Total column to equal the Percent column of this record multiplied by the Total column of the record with the previous AutoNumber (unless this is the first record, in which case it would equal the Percent column multiplied by an arbitrary starting number).

For example:

AN PER TOT
1 50% $100
2 50% $50
3 50% $25
4 50% $12.5
and so on...

Thank you in advance!

Shaun M. Brown

 

[SydneyGeek]

I don't think you can reference the previous record in a query -- you're looking at code to solve this one. Which version of Access are you using?

Denis

 

[ShaunMBrown]

I'm on Access 97. I'm sorry, I forgot to mention that.

Thanks again!

Shaun M. Brown

 

[bat17]

Also you are living dangerously by using Autonumber like that. Autonumber is designed to give a unique reference to each record not to give a consistent numbering sequence, it can miss numbers for various reasons. If somebody starts a record the "Escapes" out of it you will lose a number, if the computer crashes before a record is saved you can lose a number, and if a record is deleted there is no way to go back and reuse a number. Having said all that, if you are only using the Autonumber field to maintain a sort order of when the records were created it will work OK.

You will certainly need to use code for what you want to do, how depends on what you are doing with the output, is this going straight into a report?

Peter

 

[ShaunMBrown]

bat17,

Thanks for the advice on Autonumbers! I didn't realize that was the case. In Excel, I would probably write something that looks for the highest number that is less than the current number but that's another situation.

I'm actually trying to create a loan amortization sheet that can calculate projected payments. Most of it is pretty easy to figure out, however, the problem I'm running into is figuring out what percentage of each payment is actually going to interest. That requires information from the previous entry (i.e. principal remaining).

I would like to present it in a report but I would also like to have it remain in a query so I can reference it from other queries as well. I believe that I actually was able to reference data from a previous entry tweeking a SELECT statement that I had seen in a previous post (please forgive me, my SQL knowledge is limited). However, I attempted to clean it up and now I can not recreate it. A co-worker believes that I must have fallen asleep at my keyboard and dreamed the whole sequence but I am certain that it did work. I apologize that I can not recreate that SELECT statement, but then again, if I could, this wouldn't be a problem.

Thanks again!

Shaun M. Brown