dcount events between to limits

[Guest]

will dcount work
2 tables
First has Aircraft with Start and Stop values and unique event ID
AircraftID StratHours End Hours Event
546 50 65 xxx
546 50 51 zz
Second is list of every Aircraft and Each hour flown;
Aircraft Hr
555 1
555 2
546 50
546 51
546 52

I need the count for each aircraft / hour ie.;546 at hour 50 & 51 had 2
events each and at hour 52 had 1 event

 

[John Spencer]

The SQL for that query would look something like the following.

SELECT [AirHours].[AirCraft]
, Count([AirHours].

---

) as EventCount
FROM [AirHours] INNER JOIN [Events]
ON [AirHours].[AirCraft] = [Events].[AircraftID]
AND ([AirHours].

---

>=[Events].[StartHours] and
[AirHours].

---

<=[Events].[EndHours])
GROUP BY [AirHours].[AirCraft]

The above cannot be built in the query grid because it is using a comparison
in the join that uses something besides equals.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..