Creating a query based on dates, but not consecutive dates

[ericryd]

I have a table that has two columns. Name and date. I need to have the query pull all of the dates in the past year and report on them. I have completed that part.

The other part of this is, I have a counter that just counts how many dates the name has attached. I need to have the query check if any of the dates are consecutive, and if they are, they should be discarded.

Example:

Eric 01/02/08
Eric 04/07/08
Eric 04/08/08
Eric 05/10/08

Total: 3 (It should discard the consecutive dates and only count it as one)

Any thoughts on how to do this?

 

[EMP]

I don't think you can achieve this with a query or queries.

You'll need to use VBA code to loop through the records in a recordset.

^

 

[khawar]

Download the attached sample

 

[datAdrenaline]

Actually ... that should be no problem in a query ...

SELECT FullName, Count(*) AS ContiguousBlocks
FROM (SELECT tblSomeTable.FullName, tblSomeTable.EntryDate, vContiguous.EntryDate
FROM tblSomeTable LEFT JOIN tblSomeTable AS vContiguous
ON tblSomeTable.EntryDate = vContiguous.EntryDate + 1
WHERE vContiguous.EntryDate IS NULL) AS vTbl
GROUP BY FullName


Where FullName is your name column, EntryDate is your date column, tblSomeTable is the name of your table.

With the data the OP provide, the above query will return a single row of data ...

FullName ContiguousBlocks
Eric 3

Hope that helps!

 

[ericryd]

Awesome thanks all!

As a work around, I just added a checkbox to the table called "is unique" and when someone enters data, if there are consecutive dates, they check the box on all but the first.

Thanks again!

 

[Jon K]

As a work around, I just added a checkbox to the table called "is unique" and when someone enters data, if there are consecutive dates, they check the box on all but the first.

You have found the most efficient method as using domain aggregate functions or subqueries in a correlated way is very inefficient and should be avoided if possible.
.

 

[datAdrenaline]

Hello Jon ...

At the risk of sounding brash (which is not my intent at all !!!) ... I disagree with your encouragement that the chosen solution is the best way to accomplish the task. The OP (original poster) has found a work-around, which is commendable indeed ... but ... it is definately a work around and is now dependant upon his users entering data that is not needed. The method selected will bring the integrity of the results into question each time the summarizing query is ran. The solution I provided maintains data integrity and removes that un-needed responsibility of entering data, if the date is contiguous.

Plus ... the addition of the check box column is essentially storing the results of a calculated field, which does not fit well into a database that is designed with Normalization guidelines. So ... what happens now if someone corrects a date? How can you be sure the user will check/uncheck the continuity check box ... What happens if the user is typing happily away and they have a string of 10 contiguous dates, then there is one that is off by one ... but the miss it since they have gotting in the habit of checking a box!

Again ... in no way do I intend to sound brash or hateful or whatever ... I am presenting my viewpoint and why I feel that way.

Any thoughts in return?

 

[datAdrenaline]

Hey Eric ...

You're welcome! ... And I am glad you found a work around but I urge you to consider using the method I proposed. Read my reply to Jon K for more information.

 

[boblarson]

And I would have to say -"Listen to Brent...he knows what he's talking about."

 

[Jon K]

You did make a good point in the scenario you described, Brent.

However, my saying of avoiding using domain aggregate functions and subqueries in a correlated way if possible is not without reason. The following thread showed a performance issue reported by a poster I recently tried to help by suggesting the using of correlated subqueries.
http://www.access-programmers.co.uk/forums/showthread.php?t=144321
It was not an isolated case and I can provide more examples if I do a search on my past posts on these forums. In my experience I have found that the inefficiency of correlated subquery would become noticeable when the table involved became only moderately large.


In the present case, if Eric would like to use his work around, he can let his user input data using a bound form so that he can write some code to validate the check box.

And in the case of subsequently changing a date, he can use some VBA code to validate the check box field, assuming such a need does not occur frequently.

Jon

 

[LPurvis]

Jon - have you perhaps tried re-reading what Brent says to you in his reply and looked at his query again?

Your assertion to try to avoid field dependent function calls and Corrolated subqueries is, of course, valid - it's a well accepted fact in all database development that they come at a performance price. Sometimes they're unavoidable - and the performance hit has to be taken. Sometimes a temp table can help - sometimes not.

But Brent's solution does not use a corrolated subquery.
There are nothing but Joins in the solution he offers.
It is an appropriate choice.

The currently employed boolean field is effectively a stored calculated result - introducing redundancy and the potential for inconsistent updates.
(Meaning you always have to enter the data from one location - or make sure that the checking functionality is implemented everywhere that data is editable.)

Cheers.

 

[Jon K]

Leigh,

Thanks. I re-read Brent's SQL statement and did realize the mistake that I had made the first time I read the statement.


Brent,

I'm so sorry for my mistake. Please accept my apology for having misread your SQL statement.
.

 

[datAdrenaline]

Hello Jon ...

No problemo! ...

Bob,
Thanks for the vote of confidence!! ... I truly appreciate it! ...

Leigh,
Thanks for the followup mate! ...