Query..Code..SQL...???

[Guest]

I'm embarrased to show you the many attempts I have made as solving this problem... so I'll just ask my question and hope that someone can lead me in a more direct manner to a solution..

I'm not sure where to begin... I have a report that is dealing with figuring Miles per gallon on cars within a fleet. On my report I have been able to get this calculation fine, except that the beginning odometer reading that I am using for this monthly report is the first entry of that current month. I need this odometer reading to be the last odometer reading of the previous month. I have the following query set up to give me the previous months odometer reading that I use on a report on total monthly mileage, but it gives me every month that has a record. I only want the month immediately preceeding the month of the report (based on a range that is already set up in a query). Of course there are multiple cars that I'm working with. If that's as clear as mud, I don't know what is! Hope you can decifer my rambling...anymore, I'm not even sure what I'm saying...

This is the qryLastAndCurrentOd
SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate,"mmmm") AS MNane, MAX(a.OdometerRead) AS MaxofCurrentMonth, MAX(b.OdometerRead) AS MaxofPreviousMont
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b ON (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo) AND (DateSerial(Year(a.MaintDate),Month(a.MaintDate),1)=DateSerial(Year(b.MaintDate),1+Month(b.MaintDate),1)
GROUP BY a.IDUniqueCarRecNo, Year(a.MaintDate), Format(a.MaintDate,"mmmm")

 

[John Spencer (MVP)]

The problem seems to be in the DateSerial = DateSerial area. I might try the
following modification to your SQL

DateSerial(Year(SomeDate),Month(SomeDate),0) will give you the last day of the
previous month, so perhaps you need something like:

DateSerial(Year(a.MaintDate),Month(a.MaintDate),0)=
DateSerial(Year(b.MaintDate),Month(b.MaintDate)+1,0)

The other problem comes about because you are grouping on Maintenance date, so I
might try

SELECT a.IDUniqueCarRecNo,
Max(Year(a.MaintDate)) as MaintYear,
Format(Max(a.MaintDate),"mmmm") AS MName,
MAX(a.OdometerRead) AS MaxofCurrentMonth,
MAX(b.OdometerRead) AS MaxofPreviousMonth
FROM tblMaintenanceRecord AS a LEFT JOIN tblMaintenanceRecord AS b
ON (a.IDUniqueCarRecNo=b.IDUniqueCarRecNo) AND
(DateSerial(Year(a.MaintDate),Month(a.MaintDate),0)=
DateSerial(Year(b.MaintDate),Month(b.MaintDate)+1,0)
GROUP BY a.IDUniqueCarRecNo;

 

[Guest]

But how do I get the report item to see that I want that particular peice of data?

 

[John Vinson]

But how do I get the report item to see that I want that particular peice of data?

By basing the report on a Query which retrieves that particular piece
of data.

If you would like a more specific answer, please post a more specific
question.