How to select an entire record while using a "max" function

[Guest]

I'm trying to select records from several tables at once"

SELECT Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
Nz(Max([Inspection Date]),"Not inspected") AS [Last Inspection],
Inspections.[Average Velocity]
FROM Units LEFT JOIN Inspections ON Units.[Unit ID]=Inspections.[Unit ID]
GROUP BY Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
Inspections.[Average Velocity];

The query SHOULD output something like this:

ARC-159-01 | ARC | 159 | 01 | 10/03/2007 | 239 |
ARC-179-01 | ARC | 179 | 01 | 10/01/2007 | 147 |
BSC-234-02 | BSC | 234 | 02 | 09/26/2007 | 79 |


That is, 1 record of the last time each unit was inspected. But instead, I
get:


ARC-159-01 | ARC | 159 | 01 | 02/03/2007 | 231 |
ARC-159-01 | ARC | 159 | 01 | 04/03/2007 | 232 |
ARC-159-01 | ARC | 159 | 01 | 06/03/2007 | 242 |
ARC-159-01 | ARC | 159 | 01 | 08/03/2007 | 239 |
ARC-159-01 | ARC | 159 | 01 | 10/03/2007 | 239 |
ARC-179-01 | ARC | 179 | 01 | 04/01/2007 | 142 |
ARC-179-01 | ARC | 179 | 01 | 06/01/2007 | 151 |
ARC-179-01 | ARC | 179 | 01 | 08/01/2007 | 144 |
ARC-179-01 | ARC | 179 | 01 | 10/01/2007 | 147 |
BSC-234-02 | BSC | 234 | 02 | 11/26/2006 | 80 |
BSC-234-02 | BSC | 234 | 02 | 01/26/2007 | 79 |
BSC-234-02 | BSC | 234 | 02 | 03/26/2007 | 76 |
BSC-234-02 | BSC | 234 | 02 | 05/26/2007 | 75 |
BSC-234-02 | BSC | 234 | 02 | 07/26/2007 | 78 |
BSC-234-02 | BSC | 234 | 02 | 09/26/2007 | 79 |

That is, EVERY time each unit has been inspected. I understand that this is
a limitation of the "MaxOf" function, but there must be a way around that.

 

[Guest]

Answered.