Return multiple results based on a field

[johnnythered]

Event#	EID	Arrived Time	Code	Cancelled Time
F1001	1		611	12:00
E1134	1	12:05	TRN
F1002	2		611	17:50
E1135	2	17:45	TRN
F1003	3	18:00	321
E1136	3	18:12	TRN
F1004	4	03:33	321
E1137	4	03:40	TRN

I need to find all EIDs where the Code is 611 where an Event# starting with F was Cancelled along with the time of the cancellation. For these results I also need the Arrived Time for the Event# starting with E for that EID.

For instance, a result I'm looking for would be:

EID	Event# (F)	Cancelled Time	Event# (E)	Arrived Time
1	F1001	12:00	E1134	12:05
2	F1002	17:50	E1135	17:45

For this example there would only be 2 rows returned.

Thanks!

[June7]

Try:

SELECT Table1.[Event#], Table1.EID, Table1.ArrivedTime, Table1.Code, Table1.CancelledTime, Table1_1.ArrivedTime
FROM Table1 AS Table1_1 INNER JOIN Table1 ON Table1_1.EID = Table1.EID
WHERE (((Table1.[Event#]) Like "F*") AND ((Table1.Code)="611") AND (Not (Table1.CancelledTime) Is Null) AND (Not (Table1_1.ArrivedTime) Is Null));