Max Date with Value List Criteria (1 Viewer)

J.Burt

New member
Local time
Today, 10:18
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.
 

SHANEMAC51

Active member
Local time
Today, 20:18
Joined
Jan 28, 2022
Messages
310
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:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:18
Joined
May 7, 2009
Messages
19,169
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

Active member
Local time
Today, 20:18
Joined
Jan 28, 2022
Messages
310
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:18
Joined
May 7, 2009
Messages
19,169
Ideally, I would like to show the medics name, with three date columns for each medics last "Hoist", "Hover Load" and "Cascade Bag" evolutions.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:18
Joined
Feb 19, 2002
Messages
42,971
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

Top Bottom