Subtract value using previous record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to develop a report that takes daily meter readings and subtracts the current record from the value of the previous record. For example, if on 1/5/2004, the field Meter1 had a value of 642, and the record for 1/4/2004 had a value in the Meter1 field of 639, the resulting difference would be 3. How can I go about doing this?
 
Hi,

My name is Eric. Thank you for using the Microsoft Access Newsgroups.

You wrote:
"I am trying to develop a report that takes daily meter readings and
subtracts the current record from the value of the previous record. For
example, if on 1/5/2004, the field Meter1 had a value of 642, and the
record for 1/4/2004 had a value in the Meter1 field of 639, the resulting
difference would be 3. How can I go about doing this?"

See the following knowledge base article:
ACC2000: Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
 
Doug said:
I am trying to develop a report that takes daily meter readings and subtracts the current record from the value of the previous record. For example, if on 1/5/2004, the field Meter1 had a value of 642, and the record for 1/4/2004 had a value in the Meter1 field of 639, the resulting difference would be 3. How can I go about doing this?
Click to expand...


I think I would try to use a subquery in the report's record
source query to calculate the previous reading. Here's the
general idea:

SELECT table.*,
(SELECT Max(X.Reading)
FROM table AS X
WHERE X.Reading < table.Reading
AND X.MeterID = table.MeterID
) AS PreviousReading

But if your report is pretty complex, it may barf on the use
of a subquery, then you may have to resort to the approaches
described in that KB article.
 
I downloaded the paper and read it as suggested. There are two options described within the paper, one that requires the table to have a number data type ID field without any missing ID values sequentially. My table does not have that. If the table doesn't, it states that one should use the Using Code section. However, the using code section describes a method to retrieve the value in a field from the previous or next record in a FORM. I am needing it in a report or a query, not a form. Therefore, I attempted to recreate my table by using a make table query that uses the DAY function to create a field that I assume is numerical using the DAY portion of the InspDate field. This results in fields containing numbers that when prompted in a parameter query for a specific data entry, I get from 28 to 31 records, depending on the month chosen, that contain the numbers 1 thru x (however many days are in that particular month). I then tried to substitute the name of this field for the ID in the paper, but I get no return in the field, all values are blank, null, whatever you want to call it.
 
Back
Top