Next row calculation in Query

[jw5107]

Below is a table showing what times an airplane blocks OUT of the ORIG location, take-OFF from the ORIG location, ON the ground at the DEST location, and blocked IN at the DEST location (the words in caps are field names). If you look at the table you can see that the DEST location on the first row is the ORIG location on the second row.
What I need to do is subtract the IN time from 1st row from the OUT time of the second row where the DEST from the 1st row equals the ORIG from the 2nd row. This will give me how long the airplane was "on the ground".

Any fixes, suggestions, examples out there???
Thanks in advance!!
jw

ORIG DEST Out Off On In
ord sdf 04:20 04:30 05:13 05:18
sdf buf 08:38 08:56 10:00 10:04

 

[SkipVought]

jw,

Here's a sample sql

SELECT S1.ORIG, S1.DEST, (S2.Out-S1.In) As TimeOnGround 
FROM MyTable S1, MyTable S2
WHERE S1.DEST = S2.ORIG

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BNPMike]

I don't think that will work. It would in the example but not generally.

I would say "Use Focus" but that's not helpful.

I honestly think for this kind of order-sensitive processing you have no option but to use VBA.

 

[SkipVought]

And why not generally?

Where is the fallacy?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BNPMike]

If this is one plane then it will travel to another destination, and another. Each time you'll get corresponding cross-products. Eventually millions of permutations. You then have to start picking the smallest gaps for any given start time. Perhaps you could do it with some grouping...

 

[Pekka]

This example is from Microsoft's query samples:
SELECT id,Dest, Orig AS next_row_orig
FROM fl
WHERE (((fl.Dest)=DLookUp("[Orig]","fl","[ID] = " & [ID]+1)));

If I understood right this will give you some hint.

Pekka

"every dog will have his day"

 

[JonFer]

Do you have a plane ID field in the table as well? Can you change the Out/Off/On/In fields to have the date and time? That would be easier to work with.

SELECT S1.PLANE, S1.ORIG, S1.DEST, S1.IN, (S2.Out-S1.In)*24 As HoursOnGround
FROM MyTable S1, MyTable S2
WHERE S1.PLANE=S2.PLANE And
S1.DEST = S2.ORIG And
S2.OUT=(Select Min(a.OUT) from MyTable A
Where a.OUT>S1.IN
And a.PLANE=S1.PLANE
And a.ORIG=S1.DEST)

 

[SkipVought]

There are OBVIOUSLY data elements that would need to be ADDED if this approch were to be expanded. TailNbr, FlightID, Date.

But "Millions"??? Give me a break!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Pekka]

Didn't I mean something following:
SELECT fl.id, fl.Dest, fl.Orig, DLookUp("[Out]","fl","[ID] = " & [ID]+1) AS Out_next, fl.[In], (DLookUp("[Out]","fl","[ID] = " & [ID]+1))-[In] AS Time_on_ground
FROM fl
WHERE (((fl.Dest)=DLookUp("[Orig]","fl","[ID] = " & [ID]+1)));




Pekka

"every dog will have his day"

 

[BNPMike]

Skip

You don't need FlightID...

 

[SkipVought]

I have added TailNbr & Date to the example. Using this query,

there are FEWER reported rows than there are Source Data Rows!

SELECT FLT1.TailNbr, FLT1.Date, FLT1.ORIG, FLT1.DEST, (FLT2.OUT-FLT1.IN) As TimeOnGrnd
FROM Flights FLT1, Flights FLT2
WHERE FLT1.DEST=FLT2.ORIG 
  AND FLT1.Date=FLT2.Date 
  AND FLT1.TailNbr=FLT2.TailNbr

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jw5107]

WOW!!!! This post sure did generate some feedback!!!

Everybody has lost me!!! The SQL that Skip posted is the closest I have ever come to figuring this out. Just not quite sure what other data points we need in order to make this work??? The table contains FLT#, FltLeg, FltDate, Tail#, etc.... Just don't know how to incornporate them into the SQL or the query.

Still working on it...
jw

 

[SkipVought]

It depends on the question(s) that need to be answered.

If the question is, "What is the Time-On-Ground for an aircraft?", then the relevant info is Tail# and Date along with the calculations. Then you narrow down the range of data by Date.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[JonFer]

Pekka - the use of [ID]+1 to get the record with the next highest Autonumber is not guaranteed to get the correct record for the next flight. It depends on how the data is entered and so is unreliable. Also, DLookup is inefficient and there is usually an SQL alternative.

 

[tubbers]

Also, DLookup is inefficient and there is usually an SQL alternative.

I'm working with DLookUp now in a make table query as well and it is VERY inefficient. Could you explain what kind of alternatives are available? I'm currently using this code:

PosU: ([PosX]-(DLookUp("[PosEchoes]![PosX]","PosEchoes","[PosEchoes]![ID]=" & [PosEchoes]![ID]+1)))

This code is repeated three times in the query to obtain PosU, PosV, and PosW.

It takes about 2 hours for the MakeTable query to run and I need a faster way of doing it. I tried writing VBA code but got lost somewhere along the line since I haven't done much coding.

 

[PHV]

Try something like this to get the basic idea:
SELECT A.ID, A.PosX, A.PosX - Nz(B.PosX,0) As PosU
FROM PosEchoes A LEFT JOIN PosEchoes B ON A.ID = B.ID - 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244