Won't select the MAX

[Guest]

I have a query to select the MAX of the inspectionID by StructureID from the
inspectionhistory table. But if there is more than 1 record per structureID
and there not identical, I get both of the records instead of the
inspectionID MAX.

EX:
InspectionID Date Inspected Issued StructureID
1 1/1/1999 (checkbox) (checkbox) 0001
2 10/6/1999 (checkbox) (checkbox) 0001

Here is the SQL:

SELECT Max(InspectionHistory.InspectionID) AS InspectionID,
InspectionHistory.Date, InspectionHistory.Inspected,
InspectionHistory.Issued, MainUnitData.StructureID
FROM (MainAreaData INNER JOIN (InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) ON MainAreaData.UnitID =
MainUnitData.UnitID) INNER JOIN InspectionHistory ON MainUnitData.StructureID
= InspectionHistory.StructureID
GROU BY InspectionHistory.Date, InspectionHistory.Inspected,
InspectionHistory.Issued, MainUnitData.StructureID
HAVING (((InspectionHistory.Date) Between CDate([Forms]![IDRForm].[SDate])
And CDate([Forms]![IDRForm].[EDate])));

Thanks!

 

[David S via AccessMonster.com]

You're getting more than one record because your GROUP BY includes
InspectionHistory.Date - you're therefore asking for the maximum value of
InspectionHistory.InspectionID of all inspections carried out on the same day.


To get the Max of InspectionID by StructureID, the simplest way would
probably to change your query to just show InspectionID instead of the Max,
and then build another query that references this query that looks like

SELECT Max(InspectionID) AS InspectionID, StructureID
FROM QueryName

It would help if you could post the table and field names if you want to
debug any further.

Cheers,
David...