Criteria controlled nested IIF statement

[tonycl69]

Hi all, my database is based around vehicle maintenance etc, so my query looks if a vehicle has been inspected, and calculates from that date to the next based on a given number of weeks, this all works fine for reporting purposes. What I would like to do is also show the last known mileage whether inspected or not. The inspected field is a yes/no. so in the criteria of the inspected column I have a yes, but this of course limits the results for the report, is there a way to show the max of mileage and if inspected or not. This is my query.

SELECT tblmain.Unit, tblWorkdone.Unitid, Max(tblWorkdone.NewJobDate) AS MaxOfNewJobDate, tblWorkdone.Inspected, tblStatus.Status, tblCategory.Category, Max(IIf([Serviced]=-1,[tblworkdone]![mileagehours]+[mileageint],[tblworkdone].[mileagehours])) AS [Next Service]
FROM ((tblmain LEFT JOIN tblWorkdone ON tblmain.Unitid = tblWorkdone.Unitid) INNER JOIN tblStatus ON tblmain.StatusID = tblStatus.StatusID) INNER JOIN tblCategory ON tblmain.CategoryID = tblCategory.CategoryID
GROUP BY tblmain.Unit, tblWorkdone.Unitid, tblWorkdone.Inspected, tblStatus.Status, tblCategory.Category


HAVING (((tblWorkdone.Inspected)=Yes) AND ((tblStatus.Status)<>"sold" And (tblStatus.Status)<>"disposed" And (tblStatus.Status)<>"Yard Use Only" And (tblStatus.Status)<>"Stolen" And (tblStatus.Status)<>"Spares Only" And (tblStatus.Status)<>"Inactive"))
ORDER BY Max(tblWorkdone.NewJobDate) DESC;

Hope this makes sense please feel free to ask any questions.

Tony

[ranman256]

make a query on MAX(milage), CarID.
do not put inspected in the query.
now join this query to your tVehicle table to pull vehicles and their max.

[tonycl69]

Thanks Ranman256, I appreciate your input, I have done what you suggested and that's great, and shows me the max mileage etc for each vehicle. When I produce the report for the workshop to show next weeks inspections, I then don't see all max(Mileage).

[ranman256]

if you are missing recs, then some records do not have a milage
either you don't want to see them
or
you need an outer join to show them... show all vehicles, but some with milage

[tonycl69]

This is what I have now:

SELECT tblmain.Unit, tblWorkdone.Unitid, Max(tblWorkdone.NewJobDate) AS MaxOfNewJobDate, tblWorkdone.Inspected, tblStatus.Status, tblCategory.Category, Max(IIf([Serviced]=-1,[tblworkdone]![MileageHours]+[mileageint])) AS [Next Service], qry_MaxOfMileageHours.[maxofmileagehours] AS lastKnownMileageHours
FROM (((tblmain LEFT JOIN tblWorkdone ON tblmain.Unitid = tblWorkdone.Unitid) INNER JOIN tblStatus ON tblmain.StatusID = tblStatus.StatusID) INNER JOIN tblCategory ON tblmain.CategoryID = tblCategory.CategoryID) LEFT JOIN qry_MaxOfMileageHours ON tblmain.Unitid = qry_MaxOfMileageHours.Unitid
GROUP BY tblmain.Unit, tblWorkdone.Unitid, tblWorkdone.Inspected, tblStatus.Status, tblCategory.Category, qry_MaxOfMileageHours.[maxofmileagehours]
HAVING (((tblWorkdone.Inspected)=Yes) AND ((tblStatus.Status)<>"sold" And (tblStatus.Status)<>"disposed" And (tblStatus.Status)<>"Yard Use Only" And (tblStatus.Status)<>"Stolen" And (tblStatus.Status)<>"Spares Only" And (tblStatus.Status)<>"Inactive"))
ORDER BY Max(tblWorkdone.NewJobDate) DESC;