Consecutive Dates/Records

[Dennis]

I have several months of information that I received when an account
information cannot be obtained. I need to pull a report that shows when an
account had been skipped for several months but consecutively, i.e. January,
February, March, without skipping a month. My table has DBID (Autonumber),
Account(Long), Skip Reason (Text), and Date.

Any help would be greatly appreciated.

 

[Michel Walsh]

Here, in your case, you can rank the data accordingly to the date:

theDate rank dateDiff("m", #01/01/1900#, theDate) AS dd
2009.01.01 1 1308
2009.02.01 2 1309
2009.04.01 3 1311
2009.05.01 4 1312
2009.06.01 5 1313




I have also computed the difference in month, from a fixed date in the past.


If you subtract the rank from the last column, and group on that value:


SELECT MIN(theDate), MAX(theDate), COUNT(*)
FROM previousQueryWithRank
GROUP BY dd - rank


you obtain:

2009.01.01 2009.02.01 2
2009.04.01 2009.06.01 3



ie, the min and max date of a consecutive presence (and the number of
consecutive month where that occurs).



So, two problems are left:

- getting data about absence (here, the algorithm operates on presence
of data, not its absence)
- getting the first query.



So, to work on data with the month were no amount is involved, I suspect you
could come with a table listing ALL the possible months, and then, run the
query wizard about finding records in your original table WITHOUT MATCH in
the table of all months (no match on the field of month, that is).


To get the first query, it is only a matter to compute the rank. Many
techniques. With Jet, if there is no duplicated values, can use a join,
like:

SELECT a.theDate, COUNT(*) as rank,
dateDiff("m", #01/01/1900#, theDate) AS dd
FROM queryWithNoMatch AS a INNER JOIN queryWithNoMatch AS b
ON a.theDate >= b.theDate
GROUP BY a.theDate





That's about it, one step at a time, it is easy.



Vanderghast, Access MVP