Count Consecutive Months - Repost

[Steve]

Sorry to re-post but I think replies were from people in a
different time zone.

Thanx, Steve.



Hi,

Thanks for the replies.

The dates are UK format. Each represents a new month.

There can only be 1 reference for each month. I want to
be able to count the number of months in a row a reference
appears.

Different conditions will apply to a reference dependent
upon the number of consecutive times it appears. i.e. 1st
= phone call, 2nd time = letter, 3rd time = invoice.

Hope this makes sense

Steve.

-----Original Message-----
John, I didn't see it that way. I thought he meant the dates 01/07/04
and 01/08/04 to be consecutive days. I hope Steve can fill us in on
his intention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Tom:

I think the user's dates are in non-US format and represent the 1st day of
consecutive months. This obviously will make the SQL a bit more difficult,
especially if it is possible to have more than one entry in a month.

Respectfully,

John Spencer

Dear Steve:

In starting to think this through, I have a question.

Is it possible to have data like this:

REFERENCE DATE
12345678 01/07/04
12345678 01/08/04
12345678 01/13/04
12345678 01/14/04
12345678 01/14/04
12345678 01/15/04

There is a problems with the above:

- if it can happen that the same REFERENCE occurs twice in the same
date, does it count as 2 or just 1.

Now for some query work.

First, eliminate the case where there is no row for the given
REFERENCE on the date prior to the given:

SELECT COUNT(*)
FROM YourTable
WHERE EXISTS (SELECT * FROM YourTable
WHERE REFERENCE = 12345678
AND [DATE] = DateAdd("d", -1, #01/09/04#)

Now, you next need to find the beginning date of each sequence of
consecutive dates for that REFERENCE. This would be a row for which
the preceding date does not exist for that REFERENCE, but there might
be more than one such sequence (an assumption). Of all such sequence
"first date" values, it would be the maximum (by date) of all those
less than the given date, again limited to those for the given
REFERENCE number. But this assumes the given date is one for which
the REFERENCE/DATE exists on the previous day.

I expect the final result you want would be the number of consecutive
dates irrespective of how many rows, which could just be found with a
DateDiff.

Does all this make sense so far? I'll spend some time coding if it
sounds like we're talking about the same thing. It may take a bit, so
I'll wait to hear back from you to see if we're on the same track.

On Wed, 15 Sep 2004 08:40:01 -0700, "Steve"

Hi,

I have a table that contains a reference field and a date
field.

I want to be able to count how many consecutive times the
reference appears in the table.

e.g.
REFERENCE DATE
12345678 01/07/04
12345678 01/08/04

This would bring back a count of 2 for the 01/09/04.

Whereas;
REFERENCE DATE
12345678 01/06/04
12345678 01/08/04

Would bring back a count of 1 for the 01/09/04.

Is it possible via a query or do I need to write some code?

Any help would be appreciated.

Cheers,
Steve.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

.

..

 

[Steve]

In answer to my own question;

I have created a function that is called from a textbox on
a form. And returns a count of consecutive records, by
using the DateSerial function.

Is there a better way?

Public Function Consecutive_NoAccess() As Integer

Set tmpRst = db.OpenRecordset("SELECT
tblMPR_Tracker.PAS_Ref, tblMPR_Tracker.MPR_Date,
tblMPR_Tracker.MRF_CODE " & _
"FROM tblMPR_Tracker
INNER JOIN tblPAS_Portfolio ON tblMPR_Tracker.MPR =
tblPAS_Portfolio.MPR " & _
"WHERE
(((tblMPR_Tracker.MPR)=" & Forms!frmReport_Viewer!
Form_Display!MPR & ") AND ((tblMPR_Tracker.Queue_Type)
='NOACC')) " & _
"ORDER BY MPR_Date
DESC;")

Dim tmpDate As Date ' Stores the date
Dim intCon_Count As Integer ' Stores the count
of Consecutive N/A's (by month)

tmpDate = tmpRst("MPR_Date")

Do Until tmpRst.EOF

If (tmpRst("MPR_Date") - tmpDate) <> 0 Then

If tmpRst("MPR_Date") <= DateSerial(Format
(tmpDate, "yy"), Format(tmpDate, "mm") - 1, Format
(tmpDate, "dd")) Then

intCon_Count = intCon_Count + 1

End If

End If

tmpDate = tmpRst("MPR_Date")

tmpRst.MoveNext

Loop

Consecutive_NoAccess = intCon_Count

End Function


Cheers,
Steve.

-----Original Message-----
Sorry to re-post but I think replies were from people in a
different time zone.

Thanx, Steve.



Hi,

Thanks for the replies.

The dates are UK format. Each represents a new month.

There can only be 1 reference for each month. I want to
be able to count the number of months in a row a reference
appears.

Different conditions will apply to a reference dependent
upon the number of consecutive times it appears. i.e. 1st
= phone call, 2nd time = letter, 3rd time = invoice.

Hope this makes sense

Steve.

-----Original Message-----
John, I didn't see it that way. I thought he meant the dates 01/07/04
and 01/08/04 to be consecutive days. I hope Steve can fill us in on
his intention.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

entry
in a month.

Respectfully,

John Spencer

Tom Ellison wrote:

Dear Steve:

In starting to think this through, I have a question.

Is it possible to have data like this:

REFERENCE DATE
12345678 01/07/04
12345678 01/08/04
12345678 01/13/04
12345678 01/14/04
12345678 01/14/04
12345678 01/15/04

There is a problems with the above:

- if it can happen that the same REFERENCE occurs twice in the same
date, does it count as 2 or just 1.

Now for some query work.

First, eliminate the case where there is no row for the given
REFERENCE on the date prior to the given:

SELECT COUNT(*)
FROM YourTable
WHERE EXISTS (SELECT * FROM YourTable
WHERE REFERENCE = 12345678
AND [DATE] = DateAdd("d", -1, #01/09/04#)

Now, you next need to find the beginning date of each sequence of
consecutive dates for that REFERENCE. This would be

a
row for which date)
of all those number
of consecutive time
coding if it the
same track. times

the

some

code?

.

..


.