Subtracting values from two consecutive records

[goodyeara]

I am having a problem, probably due to my inexperience with Access. Here is the seniero:

-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?

Example
Record #1
Date - 1/17/06
Value - 15,232

Record #2
Date - 1/16/05
Value - 14,111

Desired output
Date 1/17/06
Value - 1121

 

[Rich]

http://www.access-programmers.co.uk/forums/showthread.php?t=100243

 

[goodyeara]

Not sure how the linked thread is going to help me. I am trying to always subtract the previous days value from the current day(in the end I will have to then multipy the difference by 1000). I guess I need to know how reference any one particular value. If the answer was in the linked thread, please dumb it down, so a novice like myself can understand.

 

[Pat Hartman]

Is it really necessary to show a day by day total or would a current value solve your problem?

Calculating a day by day total is quite costly in terms of processing when it is done in a query but inexpensive when done in a report due to the differing way a recordset is processed in each case.

To show only the current value in a form, add a footer to your form. Add a control to the footer and make the ControlSource -
=Sum(youramountfield)

 

[goodyeara]

Further Detail of my problem

It is necessary, its the whole point. I'll elaborate.
I am designing a database, maybe that is my problem, to make a daily data entry/report generation easier for all involved. The operators (data enterers) will open the program, enter the all of thier numbers (into a form, which places them in a table using date as the sorted primary key). I have all of the data going to one table. To just display that data would be pointless, it would mean anything. What I am trying to have is were someone can request a report from date X to date Y. I have a query set up to collect all of the approprite (based on the type of report requested) and spit it out into a report. Here is were the calculation is important and tricky. The data value "boiler Interagrator reading" is one that is used to calculate steam production by the boiler. However, the intregrator reading itself is pointless, it is only useful if you subtract it from the previous days reading, then multiply it by 1000 to get your steam production for the previous 24 or so hours. The reading itself is actually meaningless, as it just accumulates constantly, only its difference from the previous days reading.

There are several calc. values in every report I want to make that operate in a similar way. So you see, I need this one method of calculation and I'll be off to the races.

Sorry for the lengthy discussion. Maybe I'd be better off with a Excel spreadsheet. Please Advise.

 

[Pat Hartman]

The best place to do this is in a report. Follow the link to the report solution. It involves putting the current value for a field into the tag property of the control so for each new record you can subtract the value from the .tag property from the current value of the control.

 

[WuJu]

The best place to do this is in a report. Follow the link to the report solution. It involves putting the current value for a field into the tag property of the control so for each new record you can subtract the value from the .tag property from the current value of the control.

Hello.
I have similar problem.
Could you help me out to set this to work in report?
I have a table with date and data value from output of query.
So I can make a report for this.
How do I subtract values from two consecutive records in report form?

thanks,

WuJu