query readings from current and previous month

[JRCharlie]

I have the following table in access.

I would like to query a table to show the readings from the the current month and the previous month as per next table.



Thanks in advance.

[CJ_London]

you need a more complete description - what if there is no reading this month? do you still need to see last months reading?

[ArviLaanemets]

And is it possible, that for same customer are several readings in same month, or not? And when this is possible, what will be the reading for month?

[isladogs]

Have a look at the meter readings example here http://allenbrowne.com/subquery-01.html

[JRCharlie]

Every month must have areading the only time the query would return a Null value would be on the first month a reading will be entered

[JRCharlie]

No it is not possible a customer to have more than one reading per month.

[isladogs]

Fine. So have you looked at the link I provided as the example is identical to what you want.

[JRCharlie]

Thanks Ridders52

I found the slution in your link.

SELECT Reading_tbl.rdID, Reading_tbl. Reading_Date, Reading_tbl.Reading,    (SELECT TOP 1  DUPE.Reading
   FROM Reading_tbl AS Dupe                     
   WHERE Dupe. Customer = Reading_tbl. Customer
     AND Dupe. Reading_Date < Reading_tbl. Reading_Date
   ORDER BY Dupe. Reading_Date DESC, Dupe. rdID)   AS PriorRead 
FROM Reading_tbl;

Im getting the following invalid error with this query.
When I copy this query onto FlySpeed it works fine.

[CJ_London]

from your error message it looks like you have unnecessary spaces between reading_tbl. and Reading_Date.

[isladogs]

No idea what FlySpeed is!

The error shown is caused by the space after tbl. in Reading_tbl. Reading_Date

There are a total of 6 spaces to remove (though these may be forum glitches rather than in your code)
Try this:

SELECT Reading_tbl.rdID, Reading_tbl. Reading_Date, Reading_tbl.Reading,
   (SELECT TOP 1 Dupe.Reading
   FROM Reading_tbl AS Dupe                     
   WHERE Dupe.Customer = Reading_tbl.Customer
     AND Dupe.Reading_Date < Reading_tbl.Reading_Date
   ORDER BY Dupe.Reading_Date DESC, Dupe.rdID) AS PriorRead  
   FROM Reading_tbl;

[JRCharlie]

Thanks for your help
Its woring now
After removing the spaces and also coping your query the problem still precisted, I rewrote the query now its working.
So for hlping some one else with a similar problem I realy don't know what was causing the error.

[isladogs]

To help others reading this thread, please post the corrected version that works for you

[JRCharlie]

This is the working version.

SELECT Reading_tbl.rdID, Reading_tbl.Reading_Date, Reading_tbl.Reading, (SELECT TOP 1 Dupe.Reading   FROM Reading_tbl AS Dupe                     
   WHERE Dupe.Customer = Reading_tbl.Customer         AND Dupe.Reading_Date < Reading_tbl.Reading_Date
   ORDER BY Dupe.Reading_Date DESC, Dupe.rdID) AS PriorRead
FROM Reading_tbl;

[isladogs]

Unless I'm missing something, that's exactly what I posted as the correct version in post 10!

[JRCharlie]

You are correct.
Like I previouly posted when I copied and pasted your code the same error had acured.