Last two records for a report (1 Viewer)

smithyonline · Nov 20, 2017

hello

im building a invoice database to send invoices for costumes for the power usage

i have a table with meter reading with customer id, reading, date taken

whats would be the best way to create a report showing the last and most current reading and ideally work out the different between them

thank you for any advice

Uncle Gizmo · Nov 20, 2017

Well, without knowing the details I cannot give you any specific information. However I can point you to this post on my website:- Generate Multiple Reports which you may find useful in your quest...

arnelgp · Nov 21, 2017

maybe somebody has a shorter version.
this is my version.
replace all fieldnames and table name with your fieldname and table name:

Code:

SELECT tblMeterReading.[Customer Id], (SELECT TOP 1 T1.[Reading Taken] FROM tblMeterReading AS T1 WHERE T1.[Customer Id]=tblMeterReading.[Customer Id] ORDER BY 1 DESC) As [Reading Taken], (SELECT TOP 1 T1.[Reading] FROM tblMeterReading AS T1 WHERE T1.[Customer Id]=tblMeterReading.[Customer Id] ORDER BY T1.[Reading Taken] DESC) As Reading,  (SELECT TOP 1 T1.[Reading] FROM tblMeterReading AS T1 WHERE T1.[Customer Id]=tblMeterReading.[Customer Id] AND T1.[Reading Taken] <> (SELECT TOP 1 T2.[Reading Taken] FROM tblMeterReading AS T2 WHERE T2.[Customer Id]=tblMeterReading.[Customer Id] ORDER BY 1 DESC) ORDER BY T1.[Reading Taken] DESC) As PreviousReading, (SELECT TOP 1 T1.[Reading] FROM tblMeterReading AS T1 WHERE T1.[Customer Id]=tblMeterReading.[Customer Id] ORDER BY T1.[Reading Taken] DESC)-(SELECT TOP 1 T1.[Reading] FROM tblMeterReading AS T1 WHERE T1.[Customer Id]=tblMeterReading.[Customer Id] AND T1.[Reading Taken] <> (SELECT TOP 1 T2.[Reading Taken] FROM tblMeterReading AS T2 WHERE T2.[Customer Id]=tblMeterReading.[Customer Id] ORDER BY 1 DESC) ORDER BY T1.[Reading Taken] DESC) As Consumption
FROM tblMeterReading
GROUP BY tblMeterReading.[Customer Id]

Last two records for a report (1 Viewer)

smithyonline

New member

Uncle Gizmo

Nifty Access Guy

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread