Trouble with SQL Syntax

[hthg21]

I've been working on this for way too long and can't seem to get it through my thick skull on how to get the results I want.:banghead:

I have attached 2 pics of screen shots from two different queries I'm having trouble with the one named "Trouble Query".

Here is what I'm trying to obtain:

A list of only the Hoist Operators that have been a passenger on a flight while performing either Role# 6 or 7. A return of only one record for each Hoist Operator for their most recent flight date.

This query is to keep track of their currency performing Role #6 or 7.

I think that I need an Outer Join nested in an Inner Join, but I'm not certain and every way I've tried has failed.

Thanks

 

[Galaxiom]

You just need an aggregate (AKA Totals) query. Right click in the design grid and turn on Totals. Choose Max on the date field. Put criteria on Role but don't display it. Set its Total dropdown to Where.

The outer join you have shown will list all Hoist operators. Use an inner join.

 

[hthg21]

Thanks Galaxiom!

How about if I wanted to display all Hoist Operators even if they haven't been on a flight?

How about if I wanted to display which Role they filled?