CODEWITHSUNDEEP
vbams-access
rlw2235
rlw2235

Reputation: 19

Counting Occurrences within a Range in Access

I have a Table "03_DataSelection-Appended" with several Fields example shown below:

AlarmID, AlarmTime, HourEarly, HourLate, NumAlarmsEarly

244, 10/7/19 1:25 PM, 10/7/19 12:25 PM, 10/7/19 2:25 PM, 1

245, 10/7/19 1:39 PM, 10/7/19 12:39 PM, 10/7/19 2:39 PM, 2

246, 10/7/19 1:47 PM, 10/7/19 12:47 PM, 10/7/19 2:47 PM, 3

247, 10/8/19 10:17 PM, 10/8/19 9:17 PM, 10/8/19 11:17 PM, 1

248, 10/8/19 10:17 PM, 10/8/19 9:17 PM, 10/8/19 11:17 PM, 2

249, 10/14/19 9:24 AM, 10/14/19 8:24 AM, 10/14/19 10:24 AM, 2

250, 10/14/19 9:24 AM, 10/14/19 8:24 AM, 10/14/19 10:24 AM, 2

251, 10/14/19 9:25 AM, 10/14/19 8:25 AM, 10/14/19 10:25 AM, 4

252, 10/14/19 9:25 AM, 10/14/19 8:25 AM, 10/14/19 10:25 AM, 4

What I'd like to do is Count the number of Rows that has an AlarmTime value between the AlarmTime and HourEarly for Every AlarmTime and add that value to the Table/Query as an additional Field [NumAlarmsEarly]

The Readout for the first few is shown in the field above [NumAlarmsEarly]

I have tried the standard adding AlarmTime twice to a Query and then having the second one be the "Count" but this is not returning the desired output for some reason.

Here is the code that I've tried with the Query Named "04_AlarmCounts"

SELECT A.AlarmTime, A.HourEarly, A.HourLate, 
(SELECT COUNT(*) FROM '03_DataSelection-Appended' B WHERE B.AlarmTime Between A.AlarmTime AND A.HourEarly) AS NumAlarmsEarly
FROM '03_DataSelection-Appended' AS A;

PART 2 - Adding Additional Variables

I've attempted to add additonal calcs within the nested loop but keep getting a syntax error the code I think I'm closest with is as follows:

SELECT A.AlarmTime, A.HourEarly, A.HourLate, A.MidPointBegin, A.MidPointEnd,
(SELECT COUNT(*) FROM [03_DataSelection-Appended] B WHERE B.AlarmTime Between A.AlarmTime AND A.HourEarly) AS NumAlarmsEarly
(SELECT COUNT(*) FROM [03_DataSelection-Appended] B Where B.AlarmTime Between A.AlarmTime AND A.HourLate) AS NumAlarmsLate
FROM [03_DataSelection-Appended] AS A;

I've attempted using a UNION SELECT as well as messing with the () to include both SELECT statements in the (). I'm assuming the solution is something pretty close.

Thank you in advance

Upvotes: 0

Views: 47

Answers (1)

Applecore
Applecore

Reputation: 4099

I think that you need a subquery that has SQL looking like this:

SELECT A.AlarmTime, A.HourEarly, A.HourLate, 
(SELECT COUNT(*) FROM [03_DataSelection-Appended] B WHERE B.AlarmTime Between A.AlarmTime AND A.HourEarly) AS NumAlarmsEarly
FROM [03_DataSelection-Appended] AS A;

Regards

Upvotes: 1

Related Questions