Selecting whole records while using a MaxOf function.

[Brad Granath]

Accidentally posted this in "General." Sorry.

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.

 

[Jerry Whittle]

Remove Inspections.[Average Velocity] from both the Select and Group By. Does
that get you closer to the number of records?

 

[KARL DEWEY]

Try these two queries ---
Brad_Granath ---
SELECT Inspections_1.[Unit ID], Max(Inspections_1.[Inspection Date]) AS
[MaxOfInspection Date]
FROM Inspections_1
GROUP BY Inspections_1.[Unit ID];

SELECT Units_1.[Unit ID], Units_1.Building, Units_1.[Room #], Units_1.[Hood
#], Inspections_1.[Inspection Date], Inspections_1.[Average Velocity]
FROM (Units_1 LEFT JOIN Inspections_1 ON Units_1.[Unit ID] =
Inspections_1.[Unit ID]) LEFT JOIN Brad_Granath ON Units_1.[Unit ID] =
Brad_Granath.[Unit ID]
WHERE (((Inspections_1.[Inspection Date])=[MaxOfInspection Date]))
GROUP BY Units_1.[Unit ID], Units_1.Building, Units_1.[Room #],
Units_1.[Hood #], Inspections_1.[Inspection Date], Inspections_1.[Average
Velocity];

With a subquery you could do it in one.

 

[Brad Granath]

Alright, I tried that, (modified your query to match my tables) and I get,
"Type mismatch in expression." when I try to run the query. Clicking the
help button reveals this to be error# 3615, "Type mismatch in JOIN
expression".

I've got one query called "Hood Status Pre"

SELECT Inspections.[Unit ID], Nz(Max([Inspection Date]),"Not inspected") AS
[Last Inspection]
FROM Inspections
GROUP BY Inspections.[Unit ID];

And a second query called "Hood Status"

SELECT Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
Inspections.[Inspection Date], Inspections.[Average Velocity]
FROM (Units LEFT JOIN Inspections ON Units.[Unit ID]=Inspections.[Unit ID])
LEFT JOIN [Hood Status Pre] ON Units.[Unit ID]=[Hood Status Pre].[Unit ID]
WHERE (((Inspections.[Inspection Date])=([Hood Status Pre].[Last
Inspection])))
GROUP BY Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
Inspections.[Inspection Date], Inspections.[Average Velocity];

The various JOINs look fine to me. Any ideas?

 

[KARL DEWEY]

Change to these --
SELECT Inspections.[Unit ID], Max([Inspection Date]) AS [Last Inspection]
FROM Inspections
GROUP BY Inspections.[Unit ID];

And a second query called "Hood Status"

SELECT Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
IIf([Inspection Date] Is Null,"Not Inspected",[Inspection Date]) AS [Inspect
Date], Inspections.[Average Velocity]
FROM (Units LEFT JOIN Inspections ON Units.[Unit ID]=Inspections.[Unit ID])
LEFT JOIN [Hood Status Pre] ON Units.[Unit ID]=[Hood Status Pre].[Unit ID]
WHERE (((Inspections.[Inspection Date])=([Hood Status Pre].[Last
Inspection]) OR (Inspections.[Inspection Date]) Is Null))
GROUP BY Units.[Unit ID], Units.Building, Units.[Room #], Units.[Hood #],
Inspections.[Inspection Date], Inspections.[Average Velocity];

 

[Brad Granath]

Right on the money.

Thanks a lot.