Max Date with Value List Criteria

J.Burt

New member
Local time
Today, 05:50
Joined
Mar 11, 2022
Messages
13
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.
 
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
 
Last edited:
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;
 
Any thoughts or suggestions are greatly appreciated
Code:
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
 
Ideally, I would like to show the medics name, with three date columns for each medics last "Hoist", "Hover Load" and "Cascade Bag" evolutions.
 
You start with a query to get the most recent activities and then create a crosstab of that query. The wizard will build the crosstab for you.

SELECT [Flight Card].Medic, Max([Flight Card].[Flight Date]) AS [LastFlight], [Flight Card].[Medic Activities]
FROM [Flight Card]
GROUP BY [Flight Card].Medic, [Flight Card].[Medic Activities];

If you only want activities 1, 2, 3, then you need a WHERE clause but if you want all activities, you don't.

WHERE [Flight Card].[Medic Activities] In(1,2,3)

PS, best practice is to avoid embedded spaces and special characters in ALL object names.
 

Users who are viewing this thread

Back
Top Bottom