Query/Iff/Max not working right

[KiowaPilot]

I have two tables that I am using to provide info to a report named, Pilot
ATP status Report. the First Table contains basic information about
individuals and date windows that they are required to accomplish tasks.
Named Pilots
The second table contains over 5000 lines of data that contain flight
information. Flight_Input.
I am trying to use a query to identify the most recent occurance of NG in
the FS_ID field of the Flight_Input Table for the individuals in the Pilots
Table, and still display the name of the individuals who have never recieved
this code but identify those individuals with "No NG".
I have the following SQL that provides Max Date results of Pilots that have
FS_ID that equals NG but completely leaves out Pilots who have no event of NG
in the FS_ID Field. The IIF fuction in the second SQL provides the same exact
Result.

SELECT Pilots.LName, Max(Flight_Input.FLT_DATE) AS MaxOfFLT_DATE,
Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));

SELECT Pilots.LName, IIf(Max(Flight_Input.FLT_DATE=Nz),"no
goggle",Max(FLT_DATE)) AS MaxOfFLT_DATE, Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));

 

[John Spencer]

First query get the max date for pilots with NG.
SELECT LName, Max(Flt_Date) as LastNGFlight
FROM Flight_Input
WHERE FS_ID="NG"
GROUP BY LName, Flight_Input.FS_ID

Second query using the Pilots table and the query above (qLastNGFlight)
SELECT Pilots.LName
, Q.FS_ID
, Nz(q.LastNGFlight,"No NG")
FROM Pilots LEFT JOIN qLastNGFlight as Q
ON Pilots.LName = Q.LName

OR ALL in one query
SELECT Pilots.LName
, Q.FS_ID
, Nz(q.LastNGFlight,"No NG")
FROM Pilots LEFT JOIN
(SELECT LName, Max(Flt_Date) as LastNGFlight
FROM Flight_Input
WHERE FS_ID="NG"
GROUP BY LName, Flight_Input.FS_ID) as Q
ON Pilots.LName = Q.LName
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================