I mocked up a few of your records in a table called tblTrain.
with some records
ID |
Planned Dep |
Actual Dep |
RT or Late |
OtherStuff |
1 |
00:01 |
23:54 |
|
|
2 |
06:15 |
06:18 |
|
|
3 |
06:25 |
06:17 |
|
|
4 |
08:48 |
08:43 |
|
|
5 |
08:50 |
09:13 |
|
|
6 |
14:19 |
17:29 |
|
|
7 |
08:12 |
11:28 |
|
|
Then created this Query
Code:
SELECT tblTrain.ID, tblTrain.[Planned Dep], tblTrain.[Actual Dep]
, IIf(DateDiff("n",[Planned Dep],[Actual Dep])>1300,DateDiff("n",[Planned Dep],[Actual Dep])-1440,DateDiff("n",[Planned Dep],[Actual Dep])) AS Minutes
, IIf([Minutes]<=0,"RT","LATE") AS [RT or Late]
FROM tblTrain;
To produce:
I hope it's useful to you.
Good luck.