Last two records for a report

[smithyonline]

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]

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]

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

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]