Pilot Time tracking database - related date fun...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First, thanks to the two folks that helped me with my last problem.

Now, here's the next one :^)

I'm (still) generating a pilot tracking database for my aviation company.
I've got data stored in a "Pilot Listing" table that includes dates for things that "expire" for each pilot. For example, recurrent ALSE training, check rides, medical certificates, and so on. Field names are (for example):
[Pilot_Listing].[FAA_Med_Due]
[Pilot_Listing].[ALSE_Train_Due]
[Pilot_Listing].[Check_Ride_Due]
[Pilot_Listing].[Night_Currency]
....and so on.

What I need to do with this data is turn it around to generate a Master "ToDo" list for the department.

A report would order items due by date, listing pilot and "event" due.

Offhand, I'm guessing that I'd have to do this with some sort of MakeTable query, resorting the data, but I really don't have a clue how to show what was originally the field title as data.

Since y'all were so good about helping me last time, can you recommend an approach for this?
 
You might use a union query whose SQL looks something like this:

SELECT
[Pilot_Listing].[FAA_Med_Due] AS [Event_Date],
"FAA Medical Due" AS [Event],
[Pilot_Listing].[Pilot_ID]
UNION ALL
SELECT
[Pilot_Listing].[ALSE_Train_Due] AS [Event_Date],
"ALSE Training Due" AS [Event],
[Pilot_Listing].[Pilot_ID]
UNION ALL
SELECT
[Pilot_Listing].[Check_Ride_Due] AS [Event_Date],
"Check Ride Due" AS [Event],
[Pilot_Listing].[Pilot_ID]
SELECT
[Pilot_Listing].[Night_Currency] AS [Event_Date],
"Night Currency Expires" AS [Event],
[Pilot_Listing].[Pilot_ID]
ORDER BY
[Event_Date] DESC,
[Event],
[Pilot_ID]


Richard Perry said:
First, thanks to the two folks that helped me with my last problem.

Now, here's the next one :^)

I'm (still) generating a pilot tracking database for my aviation company.
I've got data stored in a "Pilot Listing" table that includes dates for
Click to expand...
things that "expire" for each pilot. For example, recurrent ALSE training,
check rides, medical certificates, and so on. Field names are (for example):
[Pilot_Listing].[FAA_Med_Due]
[Pilot_Listing].[ALSE_Train_Due]
[Pilot_Listing].[Check_Ride_Due]
[Pilot_Listing].[Night_Currency]
...and so on.

What I need to do with this data is turn it around to generate a Master
Click to expand...
"ToDo" list for the department.
A report would order items due by date, listing pilot and "event" due.

Offhand, I'm guessing that I'd have to do this with some sort of MakeTable
Click to expand...
query, resorting the data, but I really don't have a clue how to show what
was originally the field title as data.
 
Back
Top