Find related data in a table

[paultje_bos]

All,



I am strugling with a problem. I have a flight table with departure and arrival times, each record is a follow up of the previous record (I'll illustrate it below). For a calculation I need the arrival time of the first leg and the departure time of the second leg, but I don't know how I can create a query that tells to use the times from one and an other record.

Leg	Departure Location	Arrival Location	Departure Time	Arrival Time
1	A	B	12:00	12:30
2	B	C	12:35	12:50
3	C	D	12:59	13:15
4	D	A	13:45	14:00

I want to calculate the waiting times, so on B it should say 5 min, C, 15 min, D 30 min.

I tried some small queries to determine the next leg and with this to find the departure time, but this didn't work. Hope you have some ideas!

[Homegrownandy]

What is the purpose of your database? can you briefly describe what you are doing (outside of the database) and what you want to achieve (with the database). I have a feeling this isn't how you should have it set up.

[NTC]

I assume that you have 1 more field, for the unique flight ID - that is the same value in each of the four records you display.

But what you point out is exactly correct. A database has no innate order of records - they can be resorted by any column - so that doing math between separate records is not readily at your finger tips.

There are a couple ways to do this, which are advanced:

a. set up a union or cross tab query that puts those values all in one record side-by-side and then it is relatively easy to do calculated fields in a query. When one is not familiar with union or cross tab queries it can be a little challenging to initially set those up - you'll want to research them. If your leg quantity really is 4 or less this is viable but if your real data could involve hundred of legs then this is not a viable approach.

b. change from query Design View to SQL View and write custom join syntax, along with the calculate value. Very advanced. The essence of the join logic is:
Where FlightID = FlightID AND Leg = (Leg -1)
plus you will need to fine tune this to deal with the first leg of each flight

The only easy solution is if you limit the data set to only 1 flight and there is always 4 legs (even if some are blank) as then you can write the data into fixed fields in the form or report object and then set up math using the field names.

Hope this helps

[rpeare]

Simple example, this only works because I am assuming your leg numbers are sequential and free of error:

Look at the query qryFinalQuery

I left the first step in the query as a separate object (qryLegReference) so you could see what I was doing.

paultje_bos.zip

[paultje_bos]

Sorry for the late reply, but thank you for the input. I continued with the option of rpeare, because that was doable for me. Thanks again!