Meter Readings Part 2

[libraccess]

This is further to my post the other day
I'm rebuilding an old DB --pretty well finished the bulk of it but not the meter reading part
and have been trying to fathom the subquery to put in a start meter reading based on a previous reading. (Note not necessarily "the" previous reading)
Normally a report is generated to show monthly quarterly or yearly based on Date Selection


So I have made a pared down db to try and have spent some time on this I can get the meter readings i want in separate reports but bringing it together i would really like some ideas or help please

[June7]

Have you seen this http://allenbrowne.com/subquery-01.html#AnotherRecord

[libraccess]

Yes i followed your suggestion any query i came up with showed no results

[June7]

Try:
SELECT *, (SELECT TOP 1 Dupe.RAreading FROM tblRALog As Dupe WHERE Dupe.RAassetNoFK = tblRALog.RAassetNoFK AND Dupe.MEndDate<tblRALog.MEndDate ORDER BY Dupe.MEndDate DESC, Dupe.IDRALog) AS PriorReading
FROM tblRALog;

[libraccess]

Yes that works Thanks again for your help

[nmeuvdast8]

Try:
SELECT *, (SELECT TOP 1 Dupe.RAreading FROM tblRALog As Dupe WHERE Dupe.RAassetNoFK = tblRALog.RAassetNoFK AND Dupe.MEndDate<tblRALog.MEndDate ORDER BY Dupe.MEndDate DESC, Dupe.IDRALog) AS PriorReading
FROM tblRALog;

I am trying to build a meter reading DB and ran into the same problem. I tried the code above, modified according to my DB but I am getting blank data on the previous readings

[jojowhite]

see Query1

[nmeuvdast8]

Wow! Thanks man! I've been running circles with this for about a week. You are a lofe saver!

[nmeuvdast8]

see Query1

follow up question, how can I get the previous date 2 months prior? example:

November 15, 2024
December 14, 2024
January 3, 2025
February 12, 2025

How can I get the result to show December 14, 2024?

Thanks!

[jojowhite]

i hope there won't be another last 3 month, last quarter, etc.
see Quer2, which is based on Query1.

[nmeuvdast8]

Thanks man! last question, ow can change the Previous Month and 2 Previous Month to date format?

[Welshgasman]

Just goes to show, 12 year old threads are still useful.

[June7]

Thanks man! last question, ow can change the Previous Month and 2 Previous Month to date format?

Looks like date format to me.

Keep in mind, instead of hijacking old, existing thread, should start your own. Include reference link to old if you think it can be helpful to explain your situation.