Experts,
It's been several years since I've worked in Access and I've tried to search for similar situations to mine, but so far have had no luck.
I have some data containing a timer column and three event columns. Each time an event triggers, the timer column records the time. The time between events is not constant. Multiple events can trigger at the same time. Each row is a unique time. In each event column, there can be different kind of events. Here's a sample of my table (MyEvents):
Timer Event 1 Event 2 Event 3 1 w 2 x 3 u 4 y a 5 u 6 b 7 y b 8 a 9 x 10 u 11 x 12 a 13 u 14 u 15 w 16 u 17 a 18 x
The times 1 - 18 in the first column are actual times--3:30:47, 5:19:36, etc. Each event in the first two columns is recorded as to when it starts and when it stops. So event "x" in the Event 1 column shows two starts and two stops.
I can write a query that finds the 4 unique times for the "x" event in Event 1. I know that the order of those 4 times are start, stop, start, stop. How can I add that information to the query? Or even just a counter 1, 1, 2, 2, etc. I want to use the start and stop times of "x" as parameters to find the start and stop times of all occurrences of event "a" that is between the start and stop times of each "x". Finally, I want to count how many times "u" occurs within each "a" event.
So, my initial query would show:
Time Event 1 Count 2 x 1 9 x 1 11 x 2 18 x 2
I know how to query to get the first two columns shown above, but don't know how to add a count of how many start/stop combinations of "x" have occurred. I thought I could use max(time) and min(time) from this query for each count in order to hone in on all start/stop times of "a" within each "x", counting each "a" like I did with "x". Finally, I wanted to use the results of finding the start/stops of "a" to count the number of "u" occurrences.
Any help would be greatly appreciated!
Bruce