Query problem - Help desperately needed

[Nigel Heald]

Hi,

Hopefully some kind soul can help, this has been driving me mad for past two
weeks trying to sort a solution.

Access 2003 database, 2 related tables, (1)Pilots and (2)Flight Records I
have been trying to make a query that will list all Pilots in the Pilots
table that have not flown in the past 42 days. The Flight records table
details Date of flight, Aircraft, Number of landings and flight times and is
linked to the Pilots table using a Membership Number field. Nothing I try
will give me the right answers, and it's now getting desperate. I'm sure
the answer is a simple one, hopefully, so any pointers would be most
welcome.

Many thanks in advance

Nige

 

[Allen Browne]

Try a subquery.

Something like this:
SELECT Pilots.*
FROM Pilots
WHERE NOT EXISTS
(SELECT [Flight ID] FROM [Flight Records]
WHERE (([Flight Records].[Membership Number] = Pilots.[Pilot ID])
AND ([Flight Records].[Date of flight] >= Date() - 42)));

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

I'm not an expert, but hope this helps...Create a query that calculates the
number of days since the last flight by using the Date() function - flight
date. =Date()-FlightDate Then have the criteria for that field show only
those that are >42. I think that will work.

 

[John Spencer]

A way to solve this is to identify all the pilots that HAVE flown in the
last 42 days and then use that result in an unmatched query to identify
those that have not flown.

Two Query method:

SELECT Distinct [Membership Number]
FROM [Flights Table]
WHERE [Date of Flight] > Date() -42

Save that as qHaveFlown;

Now, you can use that with the unmatched query wizard to find those that
have not flown. The wizard will build a query that looks like

SELECT [Membership Number], [PilotName]
FROM [table pilots] LEFT JOIN qHaveFlown
ON [Table Pilots].[Memberhsip Number] = qHaveFlown.[Membership Number]
WHERE qHaveFlown.[Membership Number] is Null

IF you don't know how to build a query in the SQL view and need help to
build it in the query grid, post back for detailed instructions.