Results 1 to 2 of 2
  1. #1
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25

    Average data of one table based on range selected in another table?

    Me again, with another question.

    Firstly, is there a way to average a specific set of data based on the range selected in a different table. And would anyone know how to do that?

    The range that needs to be defined is unique for every Employee ID (PartID/BPLID). Range is defined in the PD (period column).

    The data is sorted as follows:

    Table 1 has the data (.01 = 1 second, .02 = 2 seconds, and so on)



    Table 2 has the calculated number for the desired range (First Period and +5 seconds).



    So I'd like to look at Table 2 for what range needs to be averaged in Table 1. I'm sure its something simple, but I'm new to SQL/Access.

    Secondly, how do I go about averaging the 10 seconds after this desired range for each ID? (I'm guessing its just a matter of saying + 10? Or in my case +.1)

    If anyone knows how to do this, it would be greatly appreciated! :P



    Sorry for such a mess of questions. It's the last one, I promise
    Last edited by TheWolfster; 05-25-2010 at 03:19 PM.

  2. #2
    TheWolfster is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Posts
    25
    So I've gotten this:

    SELECT dat.bplid,
    seq.firstpd,
    COUNT(*),
    Avg(dat.ibi)
    FROM [H23S3] AS seq
    INNER JOIN [Time_1_Conflict_Husband] AS dat
    ON seq.bplid = dat.bplid
    AND dat.pd BETWEEN seq.firstpd AND seq.firstpd + 4
    GROUP BY dat.bplid,
    seq.firstpd
    ORDER BY dat.bplid,
    seq.firstpd

    Which in theory should work, but I can't test it and get the following error:

    Code:
    Between operator without And in query expression ".


    I've also tried slightly different formatting and gotten the same error

    SELECT dat.[bplid],
    seq.[firstpd],
    COUNT(*),
    AVG(dat.[ibi])
    FROM [H23S3] AS seq
    INNER JOIN [Time_1_Conflict_Husband] AS dat
    ON seq.[bplid] = dat.[bplid]
    AND dat.
    [pd] BETWEEN seq.[firstpd] AND seq.[firstpd] + 4
    GROUP BY dat.[bplid],
    seq.[firstpd]
    ORDER BY dat.[bplid],
    seq.[firstpd];

    Should I make a new thread for this error?

    To make it easier to understand:

    SELECT dat.[bplid], (id field from table 1)
    seq.[firstpd],
    (field that defines the beginning of the range from table 2)
    COUNT(*),
    (counts number of rows, not needed, just for error checking)
    AVG(dat.[ibi])
    (field to be averaged from table 1)
    FROM [H23S3] AS seq
    (table 2 defined as 'seq')
    INNER JOIN [Time_1_Conflict_Husband] AS dat
    (table 1 defined as 'dat')
    ON seq.[bplid] = dat.[bplid]
    (ID fields are the same/actual join)
    AND dat.
    [pd] BETWEEN seq.[firstpd] AND seq.[firstpd] + 4 (the range that needs to be averaged as defined in table 2)
    GROUP BY dat.[bplid],
    (sorting info)
    seq.[firstpd]
    ORDER BY dat.[bplid],
    seq.[firstpd];

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

Similar Threads

  1. Replies: 4
    Last Post: 02-08-2010, 11:17 AM
  2. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  3. Replies: 4
    Last Post: 09-03-2009, 02:01 PM
  4. Replies: 0
    Last Post: 07-27-2009, 07:51 AM
  5. Replies: 3
    Last Post: 10-23-2008, 08:43 AM

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