need help on this qry

[kdg]

I have the following that gives me the last odometer reading for the current month. It works fine, but in addition to this, I need the last odometer read from the previous month. Can anyone help me!!!????

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

 

[Michel Walsh]

Hi,


Sure. :


SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate),
MAX(a.OdometerRead) As thisMonth , MAX(b.OdometerRead)
As PreviousMonth

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), Month(a.MaintDate)




Untested, so the ON clause may require an additional pair of ( ). Note that
DateSerial understand that the month 13 is, indeed, the month 1 of the
following year.



Hoping it may help,
Vanderghast, Access MVP

I have the following that gives me the last odometer reading for the

current month. It works fine, but in addition to this, I need the last
odometer read from the previous month. Can anyone help me!!!????

SELECT tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate]) AS

MaintMonth, Max(tblMaintenanceRecord.OdometerRead) AS OdometerMonthlyMax

FROM tblMaintenanceRecord
WHERE (((tblMaintenanceRecord.MaintDate)>=DateAdd("m",-1,DateSerial(Year(Date()),M
onth(Date()),1))))
GROUP BY tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate]);

 

[kdg]

Thanks a bunch for your response! I really need all the help I can get. I understand where you're going... for the most part, and have attempted to create a test qry to test it. I copied in your code and when I run it I get an error stating that there is a syntax error in the JOIN operation. I'm not really adept in the code arena, so could you look it over and see if you see anything that I'm not seeing?

----- Michel Walsh wrote: -----

Hi,


Sure. :


SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate),
MAX(a.OdometerRead) As thisMonth , MAX(b.OdometerRead)
As PreviousMonth

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), Month(a.MaintDate)




Untested, so the ON clause may require an additional pair of ( ). Note that
DateSerial understand that the month 13 is, indeed, the month 1 of the
following year.



Hoping it may help,
Vanderghast, Access MVP

I have the following that gives me the last odometer reading for the

current month. It works fine, but in addition to this, I need the last
odometer read from the previous month. Can anyone help me!!!????

SELECT tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate]) AS

MaintMonth, Max(tblMaintenanceRecord.OdometerRead) AS OdometerMonthlyMax

FROM tblMaintenanceRecord
WHERE (((tblMaintenanceRecord.MaintDate)>=DateAdd("m",-1,DateSerial(Year(Date()),M
onth(Date()),1))))
GROUP BY tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate]);

 

[Michel Walsh]

Hi,


I tried a similar query in Northwind and it works fine:


SELECT a.CustomerID, Year(a.OrderDate), Month(a.OrderDate),
Max(a.ShippedDate) AS MaxOfShippedDate, Max(b.ShippedDate) AS
MaxOfShippedDate1
FROM Orders AS a LEFT JOIN Orders AS b
ON ( a.CustomerID = b.CustomerID )
AND ( DateSerial(Year(a.OrderDate), Month(a.OrderDate), 1) =
DateSerial(Year(b.OrderDate), 1+Month(b.OrderDate), 1))

GROUP BY a.CustomerID, Year(a.OrderDate), Month(a.OrderDate)



Note that if you are outside Access, that may be the problem, since
DateSerial is a VBA function, and that would work only if you are in Jet +
Access. (It won't work in C++ using Jet, as example).




Hoping it may help,
Vanderghast, Access MVP

Thanks a bunch for your response! I really need all the help I can get. I

understand where you're going... for the most part, and have attempted to
create a test qry to test it. I copied in your code and when I run it I get
an error stating that there is a syntax error in the JOIN operation. I'm not
really adept in the code arena, so could you look it over and see if you see
anything that I'm not seeing?

----- Michel Walsh wrote: -----

Hi,


Sure. :


SELECT a.IDUniqueCarRecNo, Year(a.MaintDate), Month(a.MaintDate),
MAX(a.OdometerRead) As thisMonth , MAX(b.OdometerRead)
As PreviousMonth

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), Month(a.MaintDate)




Untested, so the ON clause may require an additional pair of ( ). Note that
DateSerial understand that the month 13 is, indeed, the month 1 of the
following year.



Hoping it may help,
Vanderghast, Access MVP

I have the following that gives me the last odometer reading for

the
current month. It works fine, but in addition to this, I need the last
odometer read from the previous month. Can anyone help me!!!????

SELECT tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate])

AS
MaintMonth, Max(tblMaintenanceRecord.OdometerRead) AS OdometerMonthlyMax

FROM tblMaintenanceRecord
WHERE

(((tblMaintenanceRecord.MaintDate)>=DateAdd("m",-1,DateSerial(Year(Date()),M

onth(Date()),1))))
GROUP BY tblMaintenanceRecord.IDUniqueCarRecNo, Month([MaintDate]);

 

[kdg]

One more thing... I hope... is there a way to make this a make table query that I can work from for other queries and reports? There are other applications for using such a query, such as finding the odometer read for the last record where the maintenance category = 2 (oil change).

Also, is there a way for the query to identify the month as a month name (Jan, Feb, etc.)... especially on the report side?

I'm sorry to keep bothering you, it's just that I've been asked to build something that is slightly over my head, and I'm SOOOO thankful for this forum to get some direction. Believe it or not, I'm doing a lot on my own, but am getting stuck at this stuff.

 

[Michel Walsh]

Hi,

You can add a WHERE clause:



WHERE a.maintenanceCategory = 2


To get the month name, you can FORMAT the date (since any year do, I use
2000 instead of Year(a.MaintDate) ) :



Format( DateSerial( 2000, Month( a.MaintDate ), 1), "mmm" )




Hoping it may help,
Vanderghast, Access MVP

One more thing... I hope... is there a way to make this a make table query

that I can work from for other queries and reports? There are other
applications for using such a query, such as finding the odometer read for
the last record where the maintenance category = 2 (oil change).

Also, is there a way for the query to identify the month as a month name

(Jan, Feb, etc.)... especially on the report side?

I'm sorry to keep bothering you, it's just that I've been asked to build

something that is slightly over my head, and I'm SOOOO thankful for this
forum to get some direction. Believe it or not, I'm doing a lot on my own,
but am getting stuck at this stuff.