-----Original Message-----
Dear Steve:
I've been thinking about the dates being for consecutive months, and
although the mechanism is a bit more complex, the principle is the
same.
I would first like to serialize the months. This assigns an integer
to every month, and that integer can be used to check for the
sequential nature of the references.
I suggest you pick a date that precedes any of these references to
represent Month 0. Which you pick is arbitrary. I'll use 1/1/1900.
Using this, create a query on your references like this:
SELECT REFERENCE, [DATE],
DateDiff("m", #1/1/1900#, [DATE]) AS MonthSerial
FROM YourTable
Put the actual name of YourTable in this. Save this query. I'll
call it qryReference. Look at the results. Does it serialize the
dates into month numbers in a way that represents how you mean to
check for "consecutive"? When I did this, it put January 31 and
February 1 in different months, so they would be consecutive. January
30 and January 31 are in the same month.
Next, I need to know about this. If you ask "how many consecutive
months are there at February, 2004" and there are rows for January,
2004; February, 2004; and March, 2004 what is the answer? Is it 1,
because there is only 1 month prior to that? Or do you want to see 3
for the 3 months that are consecutive at that point? I'm building
this assuming you want the answer "1" above.
Using the above query, you can find the first month of each series of
consecutive months:
SELECT REFERENCE, [DATE], MonthSerial
FROM qryReference Q
WHERE NOT EXISTS(SELECT * FROM qryReference Q1
WHERE Q1.REFERENCE = Q.REFERENCE
AND Q1.MonthSerial = Q.MonthSerial - 1)
This query I'll save and call qryRefSeries.
You can query the above to find the maximum MonthSerial which is less
than (or equal to?) the MonthSerial of the target date. The
difference in the MonthSerial of the target date and the MonthSerial
of the row found is the number of consecutive months prior to that
date which are found in the table. Something like:
SELECT REFERENCE, [DATE], MonthSerial, TargetDate,
DateDiff("m", #1/1/1900#, TargetDate) AS TargetSerial,
DateDiff("m", #1/1/1900#, TargetDate) - MonthSerial
AS ConsecutiveMonthCount
FROM qryRefSeries Q
WHERE REFERENCE = [MyReference]
AND MonthSerial = (SELECT MAX(MonthSerial) FROM qryRefSeries Q1
WHERE Q1.REFERENCE = Q.REFERENCE
AND Q1.MonthSerial < DateDiff("m", #1/1/1900#, TargetDate))
This is a parameter query that should prompt you for TargetDate and
MyReference. I think this is what you intended for the basis on which
to return the results you want.
I've included more columns than you absolutely need, but they are
there to help in debugging and testing the results.
I hope I hit this right. It's a bit complex to just start typing and
hope to get it right. My apologies if it isn't all there. If it
doesn't work just yet, help me toward fixing it up.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
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
On Wed, 15 Sep 2004 20:42:54 -0400, "John Spencer (MVP)"
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
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
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
.
.
