Max Date with Value List Criteria

[J.Burt]

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.

 

[SHANEMAC51]

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;

your query uses 1 table, but at least 3 of them should be penalized, not counting directories
1 flights(code, date, route, reason for flight)
2 flight+ people(code, flight code, person code, person position)
3 useful human activity(code,code flights+ people, activities, what additions)

with such a bunch of tables, you will get what you want

 

[arnelgp]

SELECT [Flight Card].Medic,
Dmax("[Flight Date]", "[Flight Card]", "Medic='" & [Medic] & "' And [Medic Activities]=1") As [Last Hoist Date],
Dmax("[Flight Date]", "[Flight Card]", "Medic='" & [Medic] & "' And [Medic Activities]=2") As [Last Hover Load Date],
Dmax("[Flight Date]", "[Flight Card]", "Medic='" & [Medic] & "' And [Medic Activities]=3") As [Last Cascade Bag Date]
FROM [Flight Card]
GROUP BY [Flight Card].Medic;

 

[SHANEMAC51]

Any thoughts or suggestions are greatly appreciated

SELECT  Medic, Max( [Flight Date]) AS [LastFlight], [Medic Activities]
FROM [Flight Card] WHERE  [Medic Activities].Value = "1" GROUP BY  Medic
union all
SELECT  Medic, Max( [Flight Date]) AS [LastFlight], [Medic Activities]
FROM [Flight Card] WHERE  [Medic Activities].Value = "2" GROUP BY  Medic
union all
SELECT  Medic, Max( [Flight Date]) AS [LastFlight], [Medic Activities]
FROM [Flight Card] WHERE  [Medic Activities].Value = "3" GROUP BY  Medic
order by 1,2

 

[arnelgp]

Ideally, I would like to show the medics name, with three date columns for each medics last "Hoist", "Hover Load" and "Cascade Bag" evolutions.