Transpose and calculate- Challenge

[simbob]

Heres a good one folks! I have data in the following format:

Location Time
LOCA 00:00
LOCA 00:45
LOCB 00:50
LOCB 01:50
LOCC 01:55
LOCC 02:15

I need to get the data into this format:

LoctaionFrom LocationTo TimeBetween(min)
LOCA LOCA 45
LOCA LOCB 5
LOCB LOCB 60
LOCB LOCC 5
LOCC LOCC 20

Where the TimeBetween is the time between locations/rows!!
Looks simple but I can't nut it out!

Thanks...
Simon....

 

[Duane Hookom]

Assuming your table is named "tblSimBob":

SELECT tblSimBob.Location AS FromLocation,
tblSimBob_1.Location AS LocationTo,
DateDiff("n",[tblSimBob].[TheTime],[tblSimBob_1].[TheTime]) AS TimeBetween
FROM tblSimBob, tblSimBob AS tblSimBob_1
WHEREtblSimBob_1.TheTime=
(SELECT Min(TheTime)
FROM tblSimBob sb
WHERE sb.TheTime > tblSimBob.TheTime);

 

[John Nurick]

Here's a query that does 95% of the job.

SELECT A.Location As FromLocation,
B.Location AS ToLocation,
A.TheTime As StartTime,
(SELECT MIN(TheTime) FROM Table2 WHERE TheTime > A.TheTime) AS EndTime
FROM Table2 AS A, Table2 AS B
WHERE B.TheTime = (SELECT MIN(TheTime) FROM Table2 WHERE TheTime >
A.TheTime)
ORDER BY A.TheTime;

Your challenge is to tweak it so it uses your table and field names and
returns TimeBetween in minutes instead of StartTime and EndTime as date/time
values: