how to retrieve a value from the previous record

[JD]

Hello,
I want to retrieve a value from the previous record using a query. The best
solution for me would be a user defined function let's say preval(), then i
would like to calculate an expression as preval(a) where a is the field
whose value i need.for example:

a b prev(a) prev(b)
1 2 null null
3 4 1 2
5 6 3 4

The are some articles in knowledge base ,but none will help me.
Thank you
Jack

[Van T. Dinh]

Access stores Records in the Table UNORDERED like things in a bucket.
Hence, you need to define some sorting / ordering criteria and use a
sub-query to retrieve the "previous" Record in your sorting / ordering
criteria.

Check Access Help on SubQuery.

Extract from one of my earlier replies on a similar question:

********
It is actually not trivial as you want to refer to 2 Records as the same
time. Try a Self Join with a Subquery. The SQL String should be something
like:

SELECT [tblReading].[Datefield], [tblReading].[Reading],
[tblReading_1].[Datefield], [tblReading_1].[Reading],
[tblReading].[Reading] - [tblReading_1].[Reading] AS Usage
FROM tbltblReading, tblReading AS tblReading_1
WHERE tblReading_1.[Datefield] =
(SELECT Max([tblReading_2].[Datefield])
FROM tblReading AS tblReading_2
WHERE [tblReading_2].[Datefield] < tblReading.[Datefield]);

Alternatively, you can just create a Recordset sorted by date ASC, move to
first Record (if Recordset is not empty), store Reading in a Variable
"lngPrevReading". Move to second Record, (Reading - lngPrevReading) gives
you the difference (nett change). Save Reading as lngPrevReading. Move to
next Record and repeat the process until EOF.

HTH
Van T. Dinh

"Mike McGonagle" <mi...@rirrc.org> wrote in message
news:#JFZ$rJWBHA.1468@tkmsftngp07...
> I'm working on an application where I enter the reading from a meter. I
> would like to have the application automatically calculate the net change
> from the meter reading recorded in the previous record.
>
> This sounds simple enough but I can't for the life of me figure out how to
> do this in Access.
>
> Any suggestions, sample code would be greatly appreciated.
>
>
********
--
HTH
Van T. Dinh
MVP (Access)

"JD" <t@b> wrote in message news:eLvRJm0#BHA.1880@tkmsftngp04...

[j.rakb...@gmail.com]

Van,

If your still around, I saw your post and wanted to take it a few steps further.

I'm trying to build a query which lists all expenditures associated with multiple items, within a time period. The time period is the defined by the start year (BaseYear) and the period of analysis (AnalysisPeriod) + BaseYear. Thus is the base year is 2000, and the analysis period is 30 years, I'm querying all expenditures which occur between 2000 and 2030.

Expenditures are defined from the following variables:

Item Description, Year Built, Life Expectancy, One Time Life Expectancy Adjustment

Expenditures occur at the end of the life expectancy to renew the item. The trick is that the life expectancy of an item is modified once by the "One Time Life Expectancy Adjustment" value, then afterwards the follows the "Life Expectancy" value. There are multiple expenditures per item based on the life expectancy of them item within the time period of analysis. I would like to build a query which develops a list of all expenditures.

For example, I have three items.

My original raw data is the following:

TITLES: Item Description, Year Built, Life Expectancy, One Time Life Expectancy Adjustment

Item 1, 2000, 5, 2
Item 2, 2001, 7, 1
Item 3, 2005,10,0

The query would produce the following data from the above information if the BaseYear is 2000 and the analysis period is 20 years (this produces the end year of 2020):

TITLES: ExpenditureID, Item Description, ExpenditureYear


Expenditure 1, Item 1, 2007
Expenditure 2, Item 1, 2012
Expenditure 3, Item 1, 2017
Expenditure 4, Item 2, 2009
Expenditure 5, Item 2, 2016
Expenditure 6, Item 3, 2015


Recap:
Item 1 was built in 2000 and would have it's first renewal in 2007, because the life expectancy + one time adjustment is 7 years.
After this, it would need to be renewed in 2012 because the life expectancy of a new item is 5 years and it was last renewed in 2007.
Item 1 is then again renewed in 5 years since the last renewal (2012) and need to be renewed against in 2017 and so forth until renewal >= the end year, in this case 2020.

This listing would occur for all Items.

I really don't even know what to call this......can a macro be written so that it could be implemented into a query??????

Thanks so much, and I am sorry for my crazy descriptions....hopefully you can help....