Keep first and last record for each bus and suppres all other records for each bus 2

[capronton]

I would like to suppress all records between the first Pull Out record and the last Pull In record for each bus in the data set below.
For example, how could I suppress row 2, 3,and 4, but keep row 1 and row 5 for bus 2253 in the data set below?

tDate incTime schTime bus blk incName TripType
1/20/16 5:13:06 5:20:00 2253 5004 DEADHEAD Pull Out
1/20/16 5:35:28 5:45:00 2253 5004 DEADHEAD Pull Out
1/20/16 22:37:10 22:41:00 2253 5004 DEADHEAD Pull In
1/20/16 22:56:18 23:00:00 2253 5004 DEADHEAD Pull In
1/20/16 22:56:34 23:00:00 2253 5004 LOGOFF Pull In
1/20/16 4:35:28 4:40:00 2354 5001 DEADHEAD Pull Out
1/20/16 4:54:12 5:06:00 2354 5001 DEADHEAD Pull Out
1/20/16 0:12:20 0:16:00 2354 5001 DEADHEAD Pull In
1/20/16 0:30:54 0:35:00 2354 5001 DEADHEAD Pull In
1/20/16 0:31:24 0:35:00 2354 5001 LOGOFF Pull In
1/20/16 4:45:14 4:50:00 2356 5002 DEADHEAD Pull Out
1/20/16 5:03:42 5:15:00 2356 5002 DEADHEAD Pull Out
1/20/16 20:38:00 20:36:00 2356 5002 DEADHEAD Pull In
1/20/16 20:56:34 21:01:00 2356 5002 DEADHEAD Pull In
1/20/16 20:57:11 21:01:00 2356 5002 LOGOFF Pull In
1/20/16 5:04:00 5:10:00 2371 5003 DEADHEAD Pull Out
1/20/16 5:28:32 5:36:00 2371 5003 DEADHEAD Pull Out
1/20/16 19:14:28 19:15:00 2371 5003 DEADHEAD Pull In
1/20/16 19:33:40 19:40:00 2371 5003 DEADHEAD Pull In
1/20/16 19:34:41 19:40:00 2371 5003 LOGOFF Pull In
1/20/16 6:53:28 7:00:00 2401 5030 DEADHEAD Pull Out
1/20/16 7:24:24 7:25:00 2401 5030 DEADHEAD Pull Out
1/20/16 8:07:04 8:03:00 2401 5030 DEADHEAD Pull In
1/20/16 8:29:22 8:15:00 2401 5030 DEADHEAD Pull In
1/20/16 8:30:24 8:15:00 2401 5030 LOGOFF Pull In

 

[Charliy]

create formula @suppress

if ONFIRSTRECORD
THEN "NO"
ELSE
IF ONLASTRECORD
THEN "NO"
ELSE IF {TABLE.BUS} = PREVIOUS({TABLE.BUS}) AND {TABLE.BUS} = NEXT({TABLE.BUS}
THEN "YES"
ELSE "NO"

and then your suppress formula for your detail is: {@suppress} = "YES

 

[bdreed35]

Solid option posted by Charily. Here is another that isn't better but just another technique to do the same thing.

-Insert a group on the bus number field.
-Delete the Group Name field that was added to the new Group Header.
-Move all the detail fields to the Group Header.
-COPY all the same fields to the Group Footer.
-Suppress the detail section

Again, same results as Charily's solution, just another method.


~Brian

 

[capronton]

I tried Charliy metthod first and it worked great. I did not know about the ONFIRSTRECORD and ONLASTRECORD functions.

Thanks a lot for both methods!

capronton.