Results 1 to 7 of 7
  1. #1
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10

    Adding "counters" in a query or max/min value as parameter within the same query

    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

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    This makes absolutely no sense to me. Maybe posting some literal values from your table will be a better illustration. Anyway, seems like a Normalization issue is at the root of this.

  3. #3
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    ItsMe,

    Try this. Each column of events can contain different types of events

    Time Event 1 Event 2 Event 3
    5/1/14/2:37 w
    5/2/14 1:11 x
    5/3/14 9:37 u
    5/6/14 21:50 y a
    5/11/14 0:27 u
    5/14/14 0:29 b
    5/15/14 13:45 y b
    5/23/14 1:43 a
    5/30/14 23:32 x
    6/4/14 6:35 u
    6/9/14 15:00 x
    6/11/14 21:38 a
    6/13/14 10:03 u
    6/20/14 15:18 u
    6/21/14 2:19 w
    6/21/14 7:07 u
    6/25/14 10:24 a
    6/25/14 20:35 x

    I can make a query showing this:
    Time Event 1
    5/2/14 1:11 x
    5/30/14 23:32 x
    6/9/14 15:00 x
    6/25/14 20:35 x

    I know that "x" started at 5/2/14 1:11 and stopped at 5/30/14 23:32, started again at 6/9/14 15:00 and stopped at 6/25/14 20:35. Within each of those start/stops, I'd like to find the start/stops of event "a" within those, such that I get output that looks like this:

    Time Event 2
    5/6/14 21:50 a
    5/23/14 1:43 a
    6/11/14 21:38 a
    6/25/14 10:24 a

    So I know there were two "cycles" of event "a", starting at 5/6/14 21:50 and stopping at 5/23/14 1:43, starting again at 6/11/14 21:38 and stopping again at 6/25/14 10:24. Sometimes "a" can happen outside of event "x" and I only want to capture those that are within event "x".

    Finally, using the results from this query, I want to count how many "u" events occurred within each cycle of "a":
    Cycle of "a" Count of "u"
    1st cycle 2
    2nd cycle 3


    Have I clarified things a little bit? My big table above was actually created by joining 3 individual tables, one of which is shown below:
    Time Event 1
    5/2/14 1:11 x
    5/6/14 21:50 y
    5/15/14 13:45 y
    5/30/14 23:32 x
    6/9/14 15:00 x
    6/25/14 20:35 x

    Would it be easier if I left the 3 tables separate and made my queries that way?

    Bruce

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    ...I know that "x" started at 5/2/14 1:11 and stopped at 5/30/14 23:32...
    How do you know 5/30/14 23:32 is a "Stop" time? I understand you can iterate the records and determine that every other record is either a start or a stop time but, creating a query based on your original table will be difficult, at best, if not impossible. I rarely use the word impossible.

    Perhaps storing the start times in their own column and the stop times in their own column will be a start towards Normalization.

  5. #5
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    Unfortunately, I don't have the option of storing start and stop times in separate columns--this is how the data comes to me. Regarding knowing start and stop times, I only need to know the "first" start in a dataset and then I can infer the rest.

    Is the issue any easier if I leave the data in their original separate tables? Can I use the result of the first query to get parameters for the 2nd query, etc.?

    Thanks!

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by bwwhite View Post
    ...I only need to know the "first" start in a dataset and then I can infer the rest...

    Thanks!
    We do not always receive data in a Normalized structure. So it is up to us to parse the data. If you are not interested in storing your data in tables that are Normalized in structure then you could run queries that would append data to temp tables.

    I would start with the start and stop times, appending data to a temp table (actually, I would write to a permanent table but...). I would then continue to write data to temp tables for the remaining entities. I would plan it out in a model/diagram. I would include surrogate keys for my Primary Key fields.

  7. #7
    bwwhite is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    10
    Thanks again! Unfortunately, I don't have the option of storing the data in normalized tables--it comes to me in "non-normalized" form in 3 separate tables, as I mentioned at the end of my previous reply. Using queries to append data to temp tables sounds reasonable.

    I thought if in the initial query for Event 1, if I could add a column in the query that counted the start/stop combinations, as I attempted to illustrate in my original post, I could somehow use that in a subsequent query to loop through the start/stop times in order to select the appropriate start/stop times in the Event 2 query and again create a counter column. Finally, I could somehow loop through the Event 2 counter column to get the total occurrences of "u" in event 3 for each "cycle" of event "a". It sounds like I might have to resort to some VBA code.

    Bruce

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-15-2014, 05:56 PM
  2. "Label" field according to parameter query
    By Ada01 in forum Queries
    Replies: 1
    Last Post: 04-09-2014, 02:48 PM
  3. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  4. Adding "Open" column to Query Result
    By premis in forum Queries
    Replies: 12
    Last Post: 05-30-2012, 03:47 PM
  5. "Like" Parameter Query - St., St, or Saint?
    By socialc in forum Queries
    Replies: 2
    Last Post: 04-09-2011, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums