Query/SQL needs help

[Guest]

I'm new at all of this (posting questions and db development - yes a rookie, so sorry for any ignorance on my part). OK... I've been pulling my hair out on this for over a week. now..

Below is the SQL on a query in which I am attempting to have a result which will give me the odometer reading of the last maintenance date of the previous month. So far it gives me the last record of the current month, but I also need the last record of the previous month. Can you help??? I feel in over my head

SELECT Last(tblMaintenanceRecord.MaintDate) AS LastOfMaintDate, Last(tblMaintenanceRecord.OdometerRead) AS LastOfOdometerRead, tblMaintenanceRecord.IDUniqueCarRecNo, DateAdd("d",-Day([MaintDate]),[MaintDate]) AS LstofPrevM, DateAdd("d",-Day(DateAdd("m",1,[MaintDate])),DateAdd("m",1,[MaintDate])) AS LstofCurr
FROM tblMaintenanceRecor
GROUP BY tblMaintenanceRecord.IDUniqueCarRecNo, DateAdd("d",-Day([MaintDate]),[MaintDate]), DateAdd("d",-Day(DateAdd("m",1,[MaintDate])),DateAdd("m",1,[MaintDate])
HAVING (((Last(tblMaintenanceRecord.MaintDate)) Between [start] And [end]))

How can I get it to find the record that contains the data for the "MaintDate" that is <= the last date of the previous month for each of the IDUniqueCarRecNo? I only want the last date (record) of that previous month. I'm attempting to take the last odometer read of the previous month and subtract it from the last reading of the current month to result in a real "total miles driven" for the current month (This calculation, I beleive, would be best done in the report, don't you think?). I have the query set up to give me a range (BETWEEN[start]and[end]) and it gives me all the current month data that I want, now I only need to see that last month's date and odometer read

THANK YOU!!!!!!! In advance

 

[Steve Schapel]

Cath,

I haven't really closely examined the whole problem here, so this is
an incomplete answer. But here are a few comments that might help you
move forward.

Firstly, the Last() function is badly named... it does not do what you
think it is doing. You should be using Max().

Secondly, I would recommend that if what you really want the latest
odometer reading for both last month and this month, why not just work
with the odometer readings, rather than messing around with the dates?

So, here's a thought...
SELECT IDUniqueCarRecNo, Month([MaintDate]) AS MaintMonth,
Max(OdometerRead) AS OdometerMonthlyMax
FROM tblMaintenanceRecord
WHERE
MaintDate>=DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))
GROUP BY IDUniqueCarRecNo, Month([MaintDate])

- Steve Schapel, Microsoft Access MVP