Flight School Query Help

[BenC2]

Hi

I am assembling an access DB for a flight school. Its quite a basic DB as i'm new to Access, though seasoned with Excel.

Some background. At a flight school, you can either fly with an instructor, known as dual, or by yourself, known as solo. Each time you fly, you will learn a particular lesson, called a sequence.

My DB collects each flight as it is completed, recording all the relevant information about each student, as well as the sequence and how long the flight was in hours, either dual or solo. I have fields for all the obvious things like name, date of flight etc. I also have seperate fields for Flight duration which are Dual or Solo. The last field is 'standard', which is a measure of whether the student successfully completed that sequence or failed.

My query is this. We would like a system to pick up when students are getting behind. It should take you no more than 16 hours to go solo. So I need a query that will output all the students who have flown more than 16 hours dual, but have not completed the sequence "T1 - First solo". That's the easy one. The more difficult query I'll illustrate with a working example;

You should complete the sequence "T2 - T/A Solo" by 25 hours. A student has done 30 hours of dual flying and 3 hours of solo flying, but has not completed that particular sequence yet.

How could I write a query so that the output is all the names of students who exceed these criteria? I have a whole series of performance gates I need to create, but I hope that in learning to do 1, I can apply the principles to all the others.

I apologize for the length of my question.

Thanks, Ben.

 

[DCrake]

Me personally, I would create a simple form that would act as a report generator. I would have a field for Dual hours, another for solo hours and a third for sequence. Then I would have a tick box that I could switch on/off.

So having completed all the necessary info the query builder would go away and say

Find me all records where Dual >= 30 And Solo >= 3 and Sequence T2 solo = False. Obviously this is in plain english and does not relate to you actual query and field names.


David

 

[BenC2]

Gday, thanks for the reply.

I understand what you're saying, and it makes sense. The only complication is when you specify to locate entries where dual >= 30 hours.

If each flight entry is only 1 hour long, how do you get it to sum the dual column for that student only, and then return just the name of that 1 student when they might have dozens of records?

Thanks.

 

[DCrake]

Presumably you have a table that records each lesson, be it dual or solo for each student. I would have a query that groups by student and sums both the dual and solo flights this way you will get the results you require.

David

 

[BenC2]

Presumably you have a table that records each lesson, be it dual or solo for each student. I would have a query that groups by student and sums both the dual and solo flights this way you will get the results you require.

David

A smarter man than me you are, my thanks.

 

[BenC2]

OK, so I'm stuck again

Lets use the CPL, commercial pilots license as the example.

I have 3 tables. The first table, 'Flights', records all the flights company wide. Name, date of flight and sequence etc. The 2nd table, listPPL, is simply a list of all students who have passed the PPL and how many TOTAL dual hours they had when they passed.

I'm trying to create the 3rd table, which is a list of all students who have completed the PPL (Private pilots licence, the one you do just before the CPL) (ie, exists on the listPPL table), and have done the 50 or more hours since they passed the PPL

So my query looks like this;

SELECT Flights.Searchname, Sum(Flights.VDO) AS CPLHours INTO CPL
FROM Flights INNER JOIN PPL ON Flights.SearchName=PPL.Searchname
WHERE (((Flights.FlightDate)<=Any (Select listPPL.FlightDate from ListPPL where Flights.Searchname = listPPL.SearchName))) 
GROUP BY Flights.Searchname
Having Sum(Flights.VDO) > [B](ppl.pplhours + 50)[/B]

This works when the bolded section is replaced with just a number! But the complication is the last section, in the having clause. (PPL.PPLHours). I want to say 'Show me all flights where the Sum of the Flight hours (vdo) is greater than the PPLHours (the number of hours at PPL) + 50.

The error is that it doesn't know what ppl.pplhours is, and asks for user input when I run it. It works as designed when I just use a manually entered number. How do I get it to open the PPL table, find the matching entry for the students name, and then use the field name 'PPLHours' and then allow me to do a mathmatical operation on it, ie, add 50 to it so I can compare it to the sum of all hours so far.

My head hurts.

 

[DCrake]

You could write a function that takes the student name (Should use pk instead) and does the calculation and return the answer.

Public Function GetTotalPPLHours(StudentId As Long) As Integer

Dim Rs As DAO.Recordset
Set Rs = CurrentDb("Select Sum(PPLHours) As Hrs From YourTable Where StudentId =" & StudentID)

If Not Rs.EOF Then
  GetTotalPPLHours = Rs("Hrs")+50
  Rs.Close 
Else
  GetTotalPPLHours = 0
End If
Set Rs = Nothing

End Function

Above is not tested and don't forget to change the table/field names for yours.

Then in your query your Having Line would read

Having Sum(Flights.VDO) > GetTotalPPLHours(SearchID)

Don't forget I have recommended you use the students primary key as the identifier as opposed to the name. You may get problems with duplicate names.

David