Selecting certain records in a one to many relationship on alternate criteria

[BICAP]

If I have these tables in a shuttle service database:

EventID CreateDate FirstDepart LastDelivery 1 1/1/10 11:31 AM 1/1/10 11:32 AM 1/1/10 12:25 PM 2 1/1/10 11:36 AM 1/1/10 11:37 AM 1/1/10 12:02 PM 3 1/1/10 11:54 AM 1/1/10 11:56 AM 1/1/10 1:04 PM
EventID RecID ShuttleID Agency Depart Arrive 1 105043 Bus4 SPEEDY 1/1/2010 11:37:55 AM 1/1/2010 11:44:55 AM 1 105044 Van4 SPEEDY 1/1/2010 11:37:36 AM 1/1/2010 11:45:10 AM 1 105045 Van6 QUICK 1/1/2010 11:38:23 AM 1/1/2010 11:45:02 AM 1 105046 Van9 SPEEDY 1/1/2010 11:38:10 AM 1/1/2010 12:02:59 PM 1 105047 Truck4 SPEEDY 1/1/2010 11:37:44 AM 1/1/2010 11:58:47 AM 1 105048 Car4 QUICK 1/1/2010 11:37:22 AM 1/1/2010 11:44:58 AM 2 105049 Bus2 SPEEDY 1/1/2010 11:58:42 AM 1/1/2010 12:29:38 PM 2 105050 Van10 SPEEDY 1/1/2010 11:56:07 AM 1/1/2010 12:28:46 PM 2 105051 Van2 SPEEDY 1/1/2010 11:59:21 AM 1/1/2010 12:54:44 PM 2 105052 Van3 FAST 1/1/2010 11:59:23 AM 1/1/2010 12:19:37 PM 2 105053 Van8 SPEEDY 1/1/2010 11:56:38 AM 1/1/2010 12:48:30 PM 2 105054 Truck2 FAST 1/1/2010 11:58:55 AM 1/1/2010 12:31:01 PM 2 105055 Car12 SPEEDY 1/1/2010 11:56:29 AM 1/1/2010 1:04:01 PM 2 105056 Car3 QUICK 1/1/2010 12:02:56 PM 1/1/2010 12:56:59 PM 3 105057 Truck6 SPEEDY 1/1/2010 1:24:03 PM 1/1/2010 1:44:50 PM 3 105058 Car66 QUICK 1/1/2010 1:24:31 PM 1/1/2010 2:29:26 PM 3 105059 Car5 QUICK 1/1/2010 2:30:50 PM 1/1/2010 2:31:35 PM
Can anyone provide insight on the best way to query for each event and get the first to depart and/or the first to arrive and or the last to arrive for a specific service? I've tried a number of combinations and it iss difficult not to get multiple results for some events. It would be great if I could ignore records with ties but that would be extra credit. I can get the first, last, etc shuttle, but I want to ask what was the first QUICK shuttle to depart for each event and get: EventID CreateDate RecID ShuttleID Depart 1 1/1/10 11:31 AM 105048 Car4 1/1/2010 11:37:22 AM 2 1/1/10 11:36 AM 105056 Car3 1/1/2010 12:02:56 PM 3 1/1/10 11:54 AM 105058 Car66 1/1/2010 1:24:31 PM

I'd also like to be able to ask what was the first van to depart and get:

EventID	CreateDate	RecID	ShuttleID	Agency	Depart
1	1/1/10 11:31 AM	105044	Van4	SPEEDY	1/1/2010 11:37:22 AM
2	1/1/10 11:36 AM	105050	Van10	SPEEDY	1/1/2010 12:02:56 PM
3	1/1/10 11:54 AM

(Or no record for event 3 if there was no van)

[ItsMe]

I can get the first, last, etc shuttle, but I want to ask what was the first QUICK shuttle to depart for each event and get:

Have you tried using the Max() and the Min() functions with the Date column? Maybe you can use one of those functions via a Totals query and also add some Criteria.

[orange]

What exactly is an Event? I don't see the link between EventID and any other field--so why are here multiples of Event 1....?

[ItsMe]

What exactly is an Event? I don't see the link between EventID and any other field--so why are here multiples of Event 1....?

I was wondering the same thing. Having this column included in a query that performs an aggregate calc may produce undesirable results.

[BICAP]

Each event has multiple shuttles assigned (one to many). I can get the first or last using min/max for each event, but if you try and get the first van or for the first [van or truck] I get multiple results per event as if I'm getting the min of that record.

[ItsMe]

I get multiple results per event as if I'm getting the min of that record.

You might want to build more than one query. In a new query, you might be able to employ an aggregate calc on one column, while only including one or two other columns. Adding and or removing columns may have an affect on your calcs. Keep in mind that you can nest queries as sub-queries.

[orange]

OK, so what exactly is an event? Can you give us an example so we understand?


Sounds like you may have:


Customers
Vehicles
Events
Shuttles

If so, you might want to describe in plain English how these inter-relate and apply to what you are trying to do.