Difference between fields on subsequent dates (1 Viewer)

HamishMcD

New member
Local time
Today, 11:12
Joined
Jun 6, 2019
Messages
2
Hi, I have a table of meter readings taken every day for a number of different meter serial numbers.

SerialNumber Created MeterRead
00020704 01/01/2019 00:00 1735000
00030704 01/01/2019 00:00 879000
00049704 01/01/2019 00:00 5632000
00020704 02/01/2019 00:00 1766000
00030704 02/01/2019 00:00 880000
00049704 02/01/2019 00:00 5637000
00020704 03/01/2019 00:00 1799000
00030704 03/01/2019 00:00 881000
00049704 03/01/2019 00:00 5641000


I need to calculate the consumption (i.e. the difference between one day and the previous) for each meter so that I get something like the below.

SerialNumber DateOf Consumption Consumption
00020704 01/01/2019 31000
00030704 01/01/2019 1000
00049704 01/01/2019 5000
00020704 02/01/2019 33000
00030704 02/01/2019 1000
00049704 02/01/2019 4000

I am using Access 2016 and have tried DLookUp and DateAdd with no joy. I am a bit of a noob but please help!

Thanks
 

HamishMcD

New member
Local time
Today, 11:12
Joined
Jun 6, 2019
Messages
2
Thanks Minty. I searched but obviously missed that site!

For the record here is the sql that worked.

SELECT RawData.ID, RawData.SerialNumber, [Created]-1 AS [DateOf Consumption], RawData.MeterRead, (SELECT TOP 1 Dupe.[MeterRead]
FROM RawData AS Dupe
WHERE Dupe.[SerialNumber] = RawData.[SerialNumber]
AND Dupe.[Created]<RawData.[Created]
ORDER BY Dupe.[Created] DESC, Dupe.[SerialNumber]) AS PriorValue, [MeterRead]-[PriorValue] AS Consumption
FROM RawData;
 

Minty

AWF VIP
Local time
Today, 11:12
Joined
Jul 26, 2013
Messages
10,354
Glad to have helped.

Bookmark Allen Brownes site - it's a wealth of great tips and samples.
 

Users who are viewing this thread

Top Bottom