Query to Count Consecutive days?

[Guest]

Hello,
I am trying to figure out a way to tell how many consecutive days there is a
schedule entry for a person in a table. I've set up a query to yield a
person-day combination to filter out entries I don't want and the resulting
query is called qryDateProjectPerson with fields PrimaryID and ScheduleDate.
What I would like is something like this:

Name StartDate ConsecutiveDays
Sally 9/22/07 8
Jane 9/15/07 10
Sally 10/25/07 11

Unfortunately, the closest I can come is by using another post which tallied
up occurrences:

SELECT DISTINCT qryDateProjectPerson.PrimaryID, (SELECT Nz(Count(*) +
Sum((Select Nz(MIN(A.ScheduleDate)) FROM qryDateProjectPerson AS A
WHERE A.ScheduleDate > B.ScheduleDate AND A.PrimaryID =
qryDateProjectPerson.PrimaryID) = DateAdd('d', 1, B.ScheduleDate)),0)
FROM qryDateProjectPerson AS B WHERE B.PrimaryID =
qryDateProjectPerson.PrimaryID) AS Occurrence
FROM qryDateProjectPerson;

Any ideas would be super-appreciated.

 

[Michel Walsh]

You have a table with one record per date (per person)?

A possible solution is to rank per person, per day:

With Jet, for a large table:
- create a new table (a temporary one), with the primary key, an
autonumber, call that field rank, and the two fields Name, and TheDate
- append the data to that table:

INSERT INTO temp(Name, theDate) SELECT Name, theDate FROM yourOriginalTable
ORDER BY Name, theDate


Now, having that temporary table, it is a simple matter to subtract the rank
from theDate:


SELECT Name, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Name, theDate-rank



Note that this query gives ALL the sequences, not just the MAXimum one, per
person. In fact, if a given person has TWO (max) sequences of 8 days, what
will we do?




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your quick response. The table is actually two tables and there
can be multiple records per person per date, but I put them into a query
because for this case I only want to know if there is an occurrence or not,
and I also want to prevent one type of record from showing up in the query.

I am looking for all occurrences for a person where they have a record for 6
consecutive days (weekends do not matter) as to prevent "overtime," day-wise.
So I'm not looking for a max, I'm looking for the first date of all
occurrences where they have 6 or more days in the schedule.

 

[Guest]

I'm thinking that for each instance of a date-person, there could be a third
row saying how many consecutive days follow that one day:

Sally 9/1 5
Sally 9/2 4
Sally 9/3 3
Sally 9/4 2
Sally 9/5 1
Sally 9/6 0

But even if this were the "best" way to do this, I don't know how to do it.

 

[John Spencer]

Assumptions:
-- One record per person per day - you said the opposite
-- DateField does not contain a time

SELECT PersonID, DateField
FROM TableA as A
WHERE 6 =
(SELECT Count(*)
FROM TableA as B
WHERE B.PersonID = A.PersonID
AND B.DateField Between A.DateField and DateAdd("d",5,A.DateField)

Since your post says there is more than one record per person per day, then
you would need to create a distinct query and use that in place of table A.
SELECT Distinct PersonID, DateField
FROM TableA

Save that query as QUniqueWorkDays. Then

SELECT PersonID, DateField
FROM QUniqueWorkDays as A
WHERE 6 =
(SELECT Count(*)
FROM QUniqueWorkDays as B
WHERE B.PersonID = A.PersonID
AND B.DateField Between A.DateField and DateAdd("d",5,A.DateField)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Just to clarify:
The responder to my post said "You have a table with one record per date
(per person)?" and I was responding that my table had multiple records but I
had already set up a query to get a unique record, "qryDateProjectPerson". =)

Substituting my fields, I get:
SELECT PrimaryID, ScheduleDate FROM qryDateProjectPerson as A WHERE 6 =
(SELECT Count(*) FROM qryDateProjectPerson as B
WHERE B.PrimaryID = A.PrimaryID AND B.ScheduleDate Between A.ScheduleDate
and DateAdd("d",5,A.ScheduleDate));

But this only returns results where there are 6 days in a row, not 7 (or
more?) How would I see anything that is greater than 6 (and it could
theoretically be infinite?) Because when I run this, I get the result of "Ann
9/13 Ann 9/14 Ann 9/15" since Ann has entries from 9/13 to 9/20 -- I would
want this to result as Ann 8 since Ann has 8 days in a row on the schedule.

Hope I'm making sense & thanks again...

 

[John Spencer]

Sorry, I misread your posting. I don't have a solution that will give
you the number of days in a row - 0 to infinity.

I imagine there is one and I may come up with an idea for it sometime in
the future. It will probably involve a cartesian join and a table of
consecutive numbers. I was thinking something like the following

SELECT TableA.PersonID, tableA.DateField, tblNumbers.TheNumber
FROM TableA, tblNumbers
WHERE (SELECT Count(*) FROM TableA as B
WHERE B.PersonID = TableA.PersonID
AND B.DateField Between tableA.DateField AND
DateAdd("D",TblNumbers.theNumber,TableA.DateField) =
tblNumbers.TheNumber

BUT, that will give you results something like

123 1/1/07 6
123 1/2/07 5
123 1/3/07 4
123 1/4/07 3
123 1/5/07 2
123 1/6/07 1
none on 1/7/07
123 1/8/07 2
123 1/9/07 1
none on 1/10/07
123 1/11/07 1

So, back to the drawing board of my mind.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get only the minimum one, WITH its run length, use one of the four methods illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Michel,

Interesting solution.

It does rely on the data being inserted into the temporary table in a specific order. I'm not sure that you can control that order with an SQL append query.

INSERT INTO TempTable (who,theDate)
SELECT Who, TheDate
FROM SourceTable
ORDER BY Who, TheDate

Using the above append query, I'm not sure that I would want to rely on the append query honoring the ORDER BY clause. It probably would, but ....

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get only the minimum one, WITH its run length, use one of the four methods illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Pieter Wijnen]

oh, it will
but I'd still run an order by to make sure of the sorting afterwards, not in the least to remind me later that the order is of importance

Pieter
Michel,

Interesting solution.

It does rely on the data being inserted into the temporary table in a specific order. I'm not sure that you can control that order with an SQL append query.

INSERT INTO TempTable (who,theDate)
SELECT Who, TheDate
FROM SourceTable
ORDER BY Who, TheDate

Using the above append query, I'm not sure that I would want to rely on the append query honoring the ORDER BY clause. It probably would, but ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get only the minimum one, WITH its run length, use one of the four methods illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

I never saw it failing, but yet, I haven't see everything... And I cannot
even give the insurance that it will continue to behave like it does in
future version, since it is NOT documented by Microsoft to "have to be" like
that, as far as I know (ie., to honor the ORDER BY before making the INSERT
INTO).

An *alternative* is to explicitly call to a Ranking 'process'. In MS SQL
Server 2005, there is a (new to 2005 version) construction that gives it,
yeah!, but with Jet, if we except the INSERT INTO ... ORDER BY..., the best
we have is something like:

SELECT a.who, a.theDate, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.who = b.who AND a.theDate >= b.theDate
GROUP BY a.who, a.theDate



(the rank values are not the same as in the INSERT INTO ... ORDER BY..., but
their use is the same; here, the rank values are 'by person' , or by
'a.who', if you refer to the field name I used)



But it would be slower than the INSERT INTO by an order of magnitude, imho
(without testing it).


Vanderghast, Access MVP



Michel,

Interesting solution.

It does rely on the data being inserted into the temporary table in a
specific order. I'm not sure that you can control that order with an SQL
append query.

INSERT INTO TempTable (who,theDate)
SELECT Who, TheDate
FROM SourceTable
ORDER BY Who, TheDate

Using the above append query, I'm not sure that I would want to rely on the
append query honoring the ORDER BY clause. It probably would, but ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been
inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get
only the minimum one, WITH its run length, use one of the four methods
illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I'm not very good at just writing functions but I spent a lot of time
thinking about it and liked the idea of a temporary table and I got the
following to give me exactly what I want in a temp table that I can query to
only show values over 6, but it is SLOW! So any improvement ideas anyone has
would be extremely welcome.

Public Function CountDays()
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCommand As String
Dim blnFound As Boolean
Dim rs As DAO.Recordset
Dim sDate As Date
Dim sPerson As String
Dim d As Date
Dim i As Integer
DoCmd.SetWarnings False
Set rs = DBEngine(0)(0).OpenRecordset("qryDateProjectPerson")
Do While Not rs.EOF
sDate = rs!ScheduleDate
sPerson = rs!PrimaryID
strCriteria1 = "[ScheduleDate] = #" & sDate - 1 & "# AND " &
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("[PrimaryID]", "qryDateProjectPerson", strCriteria1),
"x") = "x" Then
strCommand = "INSERT INTO tblScheduleDays_Local(PrimaryID,
ScheduleDate) VALUES (""" & sPerson & """,""" & sDate & """)"
DoCmd.RunSQL strCommand
d = sDate
i = 0
blnFound = True
Do Until blnFound = False
d = d + 1
strCriteria2 = "[ScheduleDate] = #" & d & "# AND " &
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("PrimaryID", "qryDateProjectPerson",
strCriteria2), "x") <> "x" Then
blnFound = True
i = i + 1
Else
blnFound = False
End If
Loop
strCommand = "Update tblScheduleDays_Local SET DaysInARow =
" & i & " WHERE ScheduleDate = #" & sDate & "# AND PrimaryID = """ & sPerson
& """"
DoCmd.RunSQL strCommand
End If
rs.MoveNext
Loop
rs.Close
End Function

 

[Michel Walsh]

Inserting one row at a time, thousand of time, in a loop, *IS* slow. Cannot
you do all the inserts with a single SQL statement? It is a matter to make a
standard SELECT query that shows what you want, don't forget the ORDER BY,
and once the query is done, change it to an INSERT rows table.


Vanderghast, Access MVP

I'm not very good at just writing functions but I spent a lot of time
thinking about it and liked the idea of a temporary table and I got the
following to give me exactly what I want in a temp table that I can query
to
only show values over 6, but it is SLOW! So any improvement ideas anyone
has
would be extremely welcome.

Public Function CountDays()
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCommand As String
Dim blnFound As Boolean
Dim rs As DAO.Recordset
Dim sDate As Date
Dim sPerson As String
Dim d As Date
Dim i As Integer
DoCmd.SetWarnings False
Set rs = DBEngine(0)(0).OpenRecordset("qryDateProjectPerson")
Do While Not rs.EOF
sDate = rs!ScheduleDate
sPerson = rs!PrimaryID
strCriteria1 = "[ScheduleDate] = #" & sDate - 1 & "# AND " &
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("[PrimaryID]", "qryDateProjectPerson", strCriteria1),
"x") = "x" Then
strCommand = "INSERT INTO tblScheduleDays_Local(PrimaryID,
ScheduleDate) VALUES (""" & sPerson & """,""" & sDate & """)"
DoCmd.RunSQL strCommand
d = sDate
i = 0
blnFound = True
Do Until blnFound = False
d = d + 1
strCriteria2 = "[ScheduleDate] = #" & d & "# AND "
&
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("PrimaryID", "qryDateProjectPerson",
strCriteria2), "x") <> "x" Then
blnFound = True
i = i + 1
Else
blnFound = False
End If
Loop
strCommand = "Update tblScheduleDays_Local SET DaysInARow =
" & i & " WHERE ScheduleDate = #" & sDate & "# AND PrimaryID = """ &
sPerson
& """"
DoCmd.RunSQL strCommand
End If
rs.MoveNext
Loop
rs.Close
End Function

I never saw it failing, but yet, I haven't see everything... And I cannot
even give the insurance that it will continue to behave like it does in
future version, since it is NOT documented by Microsoft to "have to be"
like
that, as far as I know (ie., to honor the ORDER BY before making the
INSERT
INTO).

An *alternative* is to explicitly call to a Ranking 'process'. In MS SQL
Server 2005, there is a (new to 2005 version) construction that gives
it,
yeah!, but with Jet, if we except the INSERT INTO ... ORDER BY..., the
best
we have is something like:

SELECT a.who, a.theDate, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.who = b.who AND a.theDate >= b.theDate
GROUP BY a.who, a.theDate



(the rank values are not the same as in the INSERT INTO ... ORDER BY...,
but
their use is the same; here, the rank values are 'by person' , or by
'a.who', if you refer to the field name I used)



But it would be slower than the INSERT INTO by an order of magnitude,
imho
(without testing it).


Vanderghast, Access MVP



Michel,

Interesting solution.

It does rely on the data being inserted into the temporary table in a
specific order. I'm not sure that you can control that order with an SQL
append query.

INSERT INTO TempTable (who,theDate)
SELECT Who, TheDate
FROM SourceTable
ORDER BY Who, TheDate

Using the above append query, I'm not sure that I would want to rely on
the
append query honoring the ORDER BY clause. It probably would, but ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been
inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get
only the minimum one, WITH its run length, use one of the four methods
illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for your fast response. I was able to insert rows into the local table
that have the person's name and the first date that doesn't have the day
previous worked. But how do I improve looping through each of those records
and counting how many days after that are worked?

Inserting one row at a time, thousand of time, in a loop, *IS* slow. Cannot
you do all the inserts with a single SQL statement? It is a matter to make a
standard SELECT query that shows what you want, don't forget the ORDER BY,
and once the query is done, change it to an INSERT rows table.


Vanderghast, Access MVP

I'm not very good at just writing functions but I spent a lot of time
thinking about it and liked the idea of a temporary table and I got the
following to give me exactly what I want in a temp table that I can query
to
only show values over 6, but it is SLOW! So any improvement ideas anyone
has
would be extremely welcome.

Public Function CountDays()
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCommand As String
Dim blnFound As Boolean
Dim rs As DAO.Recordset
Dim sDate As Date
Dim sPerson As String
Dim d As Date
Dim i As Integer
DoCmd.SetWarnings False
Set rs = DBEngine(0)(0).OpenRecordset("qryDateProjectPerson")
Do While Not rs.EOF
sDate = rs!ScheduleDate
sPerson = rs!PrimaryID
strCriteria1 = "[ScheduleDate] = #" & sDate - 1 & "# AND " &
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("[PrimaryID]", "qryDateProjectPerson", strCriteria1),
"x") = "x" Then
strCommand = "INSERT INTO tblScheduleDays_Local(PrimaryID,
ScheduleDate) VALUES (""" & sPerson & """,""" & sDate & """)"
DoCmd.RunSQL strCommand
d = sDate
i = 0
blnFound = True
Do Until blnFound = False
d = d + 1
strCriteria2 = "[ScheduleDate] = #" & d & "# AND "
&
"[PrimaryID] = """ & sPerson & """"
If Nz(DLookup("PrimaryID", "qryDateProjectPerson",
strCriteria2), "x") <> "x" Then
blnFound = True
i = i + 1
Else
blnFound = False
End If
Loop
strCommand = "Update tblScheduleDays_Local SET DaysInARow =
" & i & " WHERE ScheduleDate = #" & sDate & "# AND PrimaryID = """ &
sPerson
& """"
DoCmd.RunSQL strCommand
End If
rs.MoveNext
Loop
rs.Close
End Function

I never saw it failing, but yet, I haven't see everything... And I cannot
even give the insurance that it will continue to behave like it does in
future version, since it is NOT documented by Microsoft to "have to be"
like
that, as far as I know (ie., to honor the ORDER BY before making the
INSERT
INTO).

An *alternative* is to explicitly call to a Ranking 'process'. In MS SQL
Server 2005, there is a (new to 2005 version) construction that gives
it,
yeah!, but with Jet, if we except the INSERT INTO ... ORDER BY..., the
best
we have is something like:

SELECT a.who, a.theDate, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.who = b.who AND a.theDate >= b.theDate
GROUP BY a.who, a.theDate



(the rank values are not the same as in the INSERT INTO ... ORDER BY...,
but
their use is the same; here, the rank values are 'by person' , or by
'a.who', if you refer to the field name I used)



But it would be slower than the INSERT INTO by an order of magnitude,
imho
(without testing it).


Vanderghast, Access MVP



Michel,

Interesting solution.

It does rely on the data being inserted into the temporary table in a
specific order. I'm not sure that you can control that order with an SQL
append query.

INSERT INTO TempTable (who,theDate)
SELECT Who, TheDate
FROM SourceTable
ORDER BY Who, TheDate

Using the above append query, I'm not sure that I would want to rely on
the
append query honoring the ORDER BY clause. It probably would, but ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

With

temp
rank who theDate
1 Ann 2001.01.01
2 Ann 2001.01.02
3 Ann 2001.01.03
4 Ann 2002.01.01
5 Ann 2002.01.02
6 Ann 2002.01.03
7 Ann 2002.01.04
8 Ann 2002.01.05
9 Ann 2002.01.06
10 Ann 2003.01.01
11 Ann 2003.01.02
12 Ann 2003.01.03
13 Ann 2003.01.04
14 Ann 2003.01.05
15 Ann 2003.01.06
16 Ann 2003.01.07
17 Mary 2001.01.01
18 Mary 2001.01.02
19 Mary 2001.01.03
20 Mary 2001.01.04
21 Mary 2001.01.05
22 Mary 2001.01.06
23 Mary 2002.01.01



the temporary table (with Rank, an autonumber field) where have been
inserted the records (Order By who, theDate),

the query:

SELECT Who, MIN(theDate) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY Who, theDate-rank

returns all the sequences, their starting date and their run-length:


Query1
Who startingDate ConsecutiveDays
Ann 2001.01.01 3
Ann 2002.01.01 6
Ann 2003.01.01 7
Mary 2001.01.01 6
Mary 2002.01.01 1



add

HAVING COUNT(*) >= 6

to remove sequences that have their run less than 6.

That will still leave the two valid sequences from Ann, though. To get
only the minimum one, WITH its run length, use one of the four methods
illustrated at http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

I think you lost me


I assume you already have a table where there is one row for each day each
person, like:

person date
....
Sally 9/1/2007
Sally 9/2/2007
Sally 9/3/2007
Sally 9/4/2007
Sally 9/5/2007
Sally 9/6/2007
....


I assume you are trying to make the temporary table to get:

rank person date
....
x Sally 9/1/2007
x+1 Sally 9/2/2007
x+2 Sally 9/3/2007
x+3 Sally 9/4/2007
x+4 Sally 9/5/2007
x+5 Sally 9/6/2007
....


right? or you already got that table and you are stuck at some step after
that?



Vanderghast, Access MVP

 

[Guest]

Sorry...
I have a query that gives me every day a person is scheduled to work. I used
an insert procedure to get a temporary table that says the "first day" of a
block that the person works. Where I am stuck is getting a process to count
how many days after that first day of a block the person also works.

I set up a loop so that for each day in the temporary table, it will look to
the query and see if the next day is worked or not and if it is, then it
checks the next day, and so forth, and finally updates that row with how many
days follow. But that loop is kind of slow and I didn't know if there was a
way to improve it.

So the table I want would look like this

Sally 9/1/07 8
Greg 9/14/07 7
Ann 9/2/07 6
Sally 9/13/07 3

Where the number represents how many days consecutive to that first date the
person is scheduled. Then I can query the temp table if I only want to see
people scheduled for more than X number of days in a row.

Hope this makes a little more sense & thanks again...

 

[Michel Walsh]

Ok, so if you have

person when 'fields
Sally 9/1/07
Sally 9/2/07
Sally 9/3/07
Sally 9/4/07
Sally 9/5/07
Sally 9/6/07
Sally 9/7/07
Sally 9/8/07
Greg 9/14/07
Greg 9/15/07
Greg 9/16/07
Greg 9/17/07
Greg 9/18/07
Greg 9/19/07
Greg 9/20/07
Ann 9/2/07
Ann 9/3/07
Ann 9/4/07
Ann 9/5/07
Ann 9/6/07
Ann 9/7/07
Sally 9/13/07
Sally 9/14/07
Sally 9/15/07 'data


then,.STEP 1, create a temporary table, temp, with 3 fields:

rank, person, when


where rank is to be an autonumber field, and the primary key.



Next, STEP 2, run the query;


INSERT INTO temp(person, when) SELECT person, when FROM
yourOriginalTableName ORDER BY person, when


That should produce data, in table temp, to look like:





rank person when 'fields
1 Ann 9/2/07
2 Ann 9/3/07
3 Ann 9/4/07
4 Ann 9/5/07
5 Ann 9/6/07
6 Ann 9/7/07
7 Greg 9/14/07
8 Greg 9/15/07
9 Greg 9/16/07
10 Greg 9/17/07
11 Greg 9/18/07
12 Greg 9/19/07
13 Greg 9/20/07
14 Sally 9/1/07
15 Sally 9/2/07
16 Sally 9/3/07
17 Sally 9/4/07
18 Sally 9/5/07
19 Sally 9/6/07
20 Sally 9/7/07
21 Sally 9/8/07
22 Sally 9/13/07
23 Sally 9/14/07
24 Sally 9/15/07

When that is done, STEP 3, the query


SELECT person , MIN(when) AS startingDate, COUNT(*) As ConsecutiveDays
FROM temp
GROUP BY person , when-rank


should supply what you seek.





If that does not work, please specify at which step, exactly, there is a
problem.



Vanderghast, Access MVP