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

[TheWolfster]

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

[TheWolfster]

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:

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];