Good Evening,
I have a flight card database that I am attempting to use to show currency for personnel. Without listing everything the database has, for this question I am pulling three types of records (medic, flight date, medic activities) from one table [Flight Card]. The medic activities table is a value list with of "Hoist", "Hover Load" and "Cascade Bag" with corresponding numbers 1, 2, and 3 from its table.
What I am attempting to do is build a query that will list the last flight date for each medic if a condition is met from medic activities. Medic does not have its own table with their names are listed in the Row Source. Ideally, I would like to show the medics name, with three date columns for each medics last "Hoist", "Hover Load" and "Cascade Bag" evolutions. End goal is to be able to run a report daily or a couple times a week to verify currency in these items.
This is what I have so far, where I was attempting to isolate only the Hoist records for each medic but it shows multiple records for each medic unfortunately. If I take out the medic activities, and the Where line it will show the last flight for each medic, without the condition. I believe I will have to use a subquery, but all my attempts have failed.
SELECT [Flight Card].Medic, Max([Flight Card].[Flight Date]) AS [LastFlight], [Flight Card].[Medic Activities]
FROM [Flight Card]
WHERE ((([Flight Card].[Medic Activities].Value) = "1"))
GROUP BY [Flight Card].Medic;
Any thoughts or suggestions are greatly appreciated.
-John.
I have a flight card database that I am attempting to use to show currency for personnel. Without listing everything the database has, for this question I am pulling three types of records (medic, flight date, medic activities) from one table [Flight Card]. The medic activities table is a value list with of "Hoist", "Hover Load" and "Cascade Bag" with corresponding numbers 1, 2, and 3 from its table.
What I am attempting to do is build a query that will list the last flight date for each medic if a condition is met from medic activities. Medic does not have its own table with their names are listed in the Row Source. Ideally, I would like to show the medics name, with three date columns for each medics last "Hoist", "Hover Load" and "Cascade Bag" evolutions. End goal is to be able to run a report daily or a couple times a week to verify currency in these items.
This is what I have so far, where I was attempting to isolate only the Hoist records for each medic but it shows multiple records for each medic unfortunately. If I take out the medic activities, and the Where line it will show the last flight for each medic, without the condition. I believe I will have to use a subquery, but all my attempts have failed.
SELECT [Flight Card].Medic, Max([Flight Card].[Flight Date]) AS [LastFlight], [Flight Card].[Medic Activities]
FROM [Flight Card]
WHERE ((([Flight Card].[Medic Activities].Value) = "1"))
GROUP BY [Flight Card].Medic;
Any thoughts or suggestions are greatly appreciated.
-John.