Calculating differences between table entries

[YariLei]

Hello,

I have an Access database which stores data pulled in by another program from several machines in several locations. The data contains a date of the data reading at random intervals (random, because occasionally the connection fails due to machine being offline), the ID of the machine, the ID of the reading event and a numeric value. So, something like this:

Date MachineID ReadingID Data
2016-11-20 Machine123 998877 142
2016-11-21 Machine123 887766 152
2016-11-25 Machine123 665544 167
2016-11-20 Machine456 998878 168549
2016-11-22 Machine456 776655 168551
2016-11-25 Machine789 665545 35841
2016-11-20 Machine789 998879 37521
etc.

The ReadingID is always different, so each is a unique value. However, they are not in any sequence due to the way the data is being pulled.

What I would like Access to do is calculate the difference between the data pulls for each machine, ie. how much the numbers have grown since last pull. So the results would be like this:

Date MachineID ReadingID SinceLastData
2016-11-20 Machine123 998877 0
2016-11-21 Machine123 887766 10
2016-11-25 Machine123 665544 15
2016-11-20 Machine456 998878 0
2016-11-22 Machine456 776655 2
2016-11-25 Machine789 665545 0
2016-11-20 Machine789 998879 1680

Any ideas how to accomplish this? I can't get my head around telling Access what is the previous value or how to obtain it from the records for each row.

Cheers!

Best regards,

YariLei

 

[CJ_London]

you need to use a subquery to find the previous reading. Something like

 SELECT *, Data-(SELECT TOP 1 Data FROM tblReadings WHERE MachineID=T.MachineID AND Date<T.Date ORDER BY Date Desc) AS SinceLastData
 FROM tblReadings AS T
 ORDER BY MachineID, Date

Edit: Note Date is a reserved word, so recommend change it to something more meaningful like ReadingDate, otherwise you are likely to get inexplicable errors at some point

 

[YariLei]

Amazing! That worked like a charm. I've been pouring over the internets for hours, but this little code did the trick. I actually removed the date out of the calculation completely and used ReadingID<T.ReadingID instead of Date<T.Date, because all subsequent ReadingIDs for one MachineID would always be bigger than the previous (readingID number grows over time).

Thank you very much!

-YariLei

 

[CJ_London]

it's quite a 'standard' subquery, with a small adaptation, you can get cumulatives such as total to date.

Not relevant to your requirement but using the structure to demostrate

 SELECT *, Data-(SELECT sum(Data) FROM tblReadings WHERE MachineID<=T.MachineID) AS cumulative
FROM tblReadings AS T
ORDER BY MachineID, Date