How to count intermediate dates in an Access SQL query?
I'm working on a request that requires this, but for this forum question, I'll use a hypothetical scenario as I'm more interested in an approach than a one-off solution.
Suppose a child does a good job every day, his / her teacher writes down the child's name and the date he / she did a good job.
Table: Kids
*PID Name
---- ----
1 Abigail
2 Jim
3 Peter
Table: GoodJobHistory
*KidID *EventDate
----- ---------
1 1/1/2015
1 1/2/2015
1 1/3/2015
1 1/4/2015
2 1/1/2015
2 1/2/2015
3 1/1/2015
3 1/5/2015
2 1/5/2015
* refers to a table key
I want the request to trigger the following:
Query: query_GoodJobStreak
KidID EndOfStreak ConsecutiveDays
----- ----------- ---------------
1 1/4/2015 4
2 1/2/2015 2
3 1/1/2015 1
3 1/5/2015 1
2 1/5/2015 1
Mostly I'm looking for examples, resource links, or maybe even a short explanation of how I would go about doing this.
I understand that there are several resources, but they are either (a) not focused on Access, (b) only take into account the latest records and ignore historical ones, or (c) simply do not work, Thank you in advance.
source to share
There is a clear trick you can find to find a range of ranges. First, list all rows in your history table, separated by KidID and sorted by event date.
KidID EventDate Number
----- --------- ------
1 1/1/2015 1
1 1/2/2015 2
1 1/3/2015 3
1 1/4/2015 4
2 1/1/2015 1
2 1/2/2015 2
2 1/5/2015 3
3 1/1/2015 1
3 1/5/2015 2
Then find the numerical difference between the line number and the date of the event. To find the "difference" between a date and a number, we first need to convert the date to a number. Access can do this automatically because dates are actually stored as a number behind the scenes (as # of days since 01/01/1900).
KidID EventDate Number EventDateNumber Diff
----- --------- ------ --------------- ----
1 1/1/2015 1 42005 42004 a
1 1/2/2015 2 42006 42004 a
1 1/3/2015 3 42007 42004 a
1 1/4/2015 4 42008 42004 a
2 1/1/2015 1 42005 42004 b
2 1/2/2015 2 42006 42004 b
2 1/5/2015 3 42009 42006 c
3 1/1/2015 1 42005 42004 d
3 1/5/2015 2 42009 42006 e
You should see the template already. Any sequential set of values has the same difference value! I have marked five consecutive ranges with letters next to Diff. This is a simple trick you can use to split your data into sets of sequential ranges and find the result you are after.
First, we need a way to add line numbers to the original data. This can be done with a simple function DCount
(note that it starts with 0
, but the logic works the same):
SELECT GoodJobHistory.KidID,
GoodJobHistory.EventDate,
DCount("KidID", "GoodJobHistory","KidID=" & [KidID] &
" And EventDate<#" & [EventDate] & "#") AS RowNumber
FROM GoodJobHistory;
Save the query separately so that it can be referenced as a subquery (I named it GoodJobHistoryNumbered
in my example). Then a simple group based on the difference between date and RowNumber gets the result we are looking for:
SELECT GoodJobHistoryNumbered.KidID,
Max(GoodJobHistoryNumbered.EventDate) AS EndOfStreak,
Count(GoodJobHistoryNumbered.RowNumber) AS ConsecutiveDays
FROM GoodJobHistoryNumbered
GROUP BY GoodJobHistoryNumbered.KidID, [EventDate]-[RowNumber];
Result:
KidID EndOfStreak ConsecutiveDays
1 1/4/2015 4
2 1/2/2015 2
2 1/5/2015 1
3 1/1/2015 1
3 1/5/2015 1
source to share