MAX Function

[Guest]

I have a fairly complicated query that I need to incorporate the Max Date on
each record.
Here is the current query:

SELECT tblVehicle.vehFleetIDNum, tblVehicle.vehYear, tblVehicle.vehMake,
tblVehicle.vehModel, tblVehicle.vehLicensePlateNum, tblCity.citName,
tblRegistration.regExpirationDate, tblOwner.ownName, tblLeasedTo.leaName,
tblRegistration.regType
FROM (tblLeasedTo INNER JOIN (tblCity INNER JOIN (tblOwner INNER JOIN
tblVehicle ON (tblOwner.ownIDNum = tblVehicle.vehOwnerID) AND
(tblOwner.ownIDNum = tblVehicle.vehOwnerID)) ON tblCity.citID =
tblVehicle.vehLocation) ON tblLeasedTo.leaID = tblVehicle.vehLeasedTo) INNER
JOIN tblRegistration ON tblVehicle.vehVehicleIDNum =
tblRegistration.regVehicleIDNum
WHERE (((tblRegistration.regExpirationDate) Between [Enter AS OF expiration
date:] And [Enter LATEST desired expiration date:]) AND
((tblVehicle.vehSold)=False));

I need the Max function to work on the tblRegistration.regExpirationDate for
each tblVehicle.vehVehicleIDNum = tblRegistration.regVehicleIDNum.

Thank you ahead of time.

 

[DebbieG]

Add this as the criteria under your regExpirationDate field in the query grid:

(SELECT MAX(regExpirationDate) FROM tblRegistration WHERE
tblRegistration.regVehicleIDNum = tblVehicle.vehVehicleIDNum)

HTH,
Debbie


|I have a fairly complicated query that I need to incorporate the Max Date on
| each record.
| Here is the current query:
|
| SELECT tblVehicle.vehFleetIDNum, tblVehicle.vehYear, tblVehicle.vehMake,
| tblVehicle.vehModel, tblVehicle.vehLicensePlateNum, tblCity.citName,
| tblRegistration.regExpirationDate, tblOwner.ownName, tblLeasedTo.leaName,
| tblRegistration.regType
| FROM (tblLeasedTo INNER JOIN (tblCity INNER JOIN (tblOwner INNER JOIN
| tblVehicle ON (tblOwner.ownIDNum = tblVehicle.vehOwnerID) AND
| (tblOwner.ownIDNum = tblVehicle.vehOwnerID)) ON tblCity.citID =
| tblVehicle.vehLocation) ON tblLeasedTo.leaID = tblVehicle.vehLeasedTo) INNER
| JOIN tblRegistration ON tblVehicle.vehVehicleIDNum =
| tblRegistration.regVehicleIDNum
| WHERE (((tblRegistration.regExpirationDate) Between [Enter AS OF expiration
| date:] And [Enter LATEST desired expiration date:]) AND
| ((tblVehicle.vehSold)=False));
|
| I need the Max function to work on the tblRegistration.regExpirationDate for
| each tblVehicle.vehVehicleIDNum = tblRegistration.regVehicleIDNum.
|
| Thank you ahead of time.