How do I get the reading for last month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table recording monthly reading. How do I get the reading for last
month?

Any reply is highly appreciated.
 
I have a table recording monthly reading. How do I get the reading for last
month?

Any reply is highly appreciated.
Click to expand...

That depends entirely on the structure of your table. How can one
determine what month is represented by a table record? Do you have a
Date/Time field, a month name, a month and year, or what?

John W. Vinson[MVP]
 
The table has four fields, CustomerID, Year, Month and Reading. How can I get
the latest reading for every customer.

Thanks.
 
The table has four fields, CustomerID, Year, Month and Reading. How can I get
the latest reading for every customer.

Thanks.
Click to expand...

What is the datatype of Month - a text field ("August") or a numeric
field 8? Do you have some particular reason not to use an Access
Date/Time field (which sorts chronologically and has a rich set of
date parsing functions) rather than separate fields which require more
complex parsing?

What I'd suggest - given the current structure - is to create a Query
based on another Query. First create a query which includes a
Date/Time field:

SELECT CustomerID, CDate("1/" & [Month] & "/" & [Year]) As
ReadingDate, [Year], [Month], [Reading] FROM yourtable;

Save this as qryReading.

Then create a second query

SELECT CustomerID, Reading
FROM qryReading
WHERE qryReading.ReadingDate = (SELECT Max(X.[ReadingDate]) FROM
qryReading AS X WHERE X.CustomerID = qryReading.CustomerID);


John W. Vinson[MVP]
 
John Vinson said:
The table has four fields, CustomerID, Year, Month and Reading. How can I
get
the latest reading for every customer.

Thanks.
Click to expand...

SELECT CustomerID, CDate("1/" & [Month] & "/" & [Year]) As
ReadingDate, [Year], [Month], [Reading] FROM yourtable;
Click to expand...

CDate() is one of the few date-related functions that respects the user's
Regional Settings. Assuming their Short Date format set to anything other
than dd/mm/yyyy, your formula won't work: all the dates they'll get will be
in January in all cases.

SELECT CustomerID, DateSerial([Year], [Month], 1) As
ReadingDate, [Year], [Month], [Reading] FROM yourtable;

avoids that problem.
 
John Vinson said:
The table has four fields, CustomerID, Year, Month and Reading. How can I
get
the latest reading for every customer.

Thanks.
Click to expand...

SELECT CustomerID, CDate("1/" & [Month] & "/" & [Year]) As
ReadingDate, [Year], [Month], [Reading] FROM yourtable;
Click to expand...

CDate() is one of the few date-related functions that respects the user's
Regional Settings. Assuming their Short Date format set to anything other
than dd/mm/yyyy, your formula won't work: all the dates they'll get will be
in January in all cases.

SELECT CustomerID, DateSerial([Year], [Month], 1) As
ReadingDate, [Year], [Month], [Reading] FROM yourtable;

avoids that problem.
Click to expand...

Thanks Douglas! Good catch!

John W. Vinson[MVP]
 
Back
Top