DatePart

[Melinda]

I have a Vehicle database with a table for odometer
readings:

tbl_Odometer_Readings
strVehicleID
datMonthEndDate (mm/dd/yyyy)
intOdometerReading

I am trying to write a query to calculate miles driven by
subtracting odometer readings. I can get every month
except January where I have to subtract December from the
prior year. I don't use VBA very much, so if I could get
some help in criteria expression in the query, I would
appreciate it.

Melinda

 

[Michel Walsh]

Hi,



SELECT a.VehiculeID,
a.MonthEndDate,
MIN(b.OdometerReading) - LAST(a.OdometerReading) AS difference

FROM OdometerReadings As a INNER JOIN OdometerReadings As b
ON a.VehiculeID = b.VehiculeID
AND b.MonthEndDate > a.MonthEndDate

GROUP BY a.VehiculeID, a.MonthEndDate



Note that in SQL, as in recent programming languages (as C# and dot-Net),
the practice is to NOT use prefix (tbl, str, dat, int), since they add
notice more than anything really useful (strongly typed languages would
report data type mismatch at compile time, anyhow), but do as you wish.


Hoping it may help,
Vanderghast, Access MVP