Access 2000 Date Count Help

[Cy]

Morning all,

I have field that contains dates. I need to add up each time a visit
is made. Easy enough. In a query, I run the date count and all works
great. However, I need to filter out if the date of the last visit
and next visit are within 2 days of themselves. Example. They might
visit a customer on the November 21. They didn't complete the work.
They will not return on the 22nd, because it's a Holiday, so they will
return on the 23rd to finish up. I need to count that span of time as
1 visit, not 2.

Any thoughts, suggestions?

Thanks

 

[Jeff Boyce]

If the data you are storing is only DateOfVisit, then Access won't have a
way to tell that two "DatesOfVisit" actually belong to the same "visit".

Does your database have something like a VisitID?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Cy]

If the data you are storing is only DateOfVisit, then Access won't have a
way to tell that two "DatesOfVisit" actually belong to the same "visit".

Does your database have something like a VisitID?

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Yes, sorry forgot to mention that, there is a Job Name for each visit.

 

[John Spencer]

SELECT JobCode, VisitDate
FROM SomeTable
WHERE Not Exists
(SELECT *
FROM SomeTable as Temp
WHERE Temp.JobCode = SomeTable.JobCode
AND DateDiff("d",SomeTable.VisitDate, Temp.VisitDate) <=2)

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

 

[John Spencer]

Hmm, that would probably not work. Should not have been so quick to press
the send key.

SELECT JobCode, VisitDate
FROM SomeTable
WHERE Not Exists
(SELECT *
FROM SomeTable as Temp
WHERE Temp.JobCode = SomeTable.JobCode
AND Temp.VisitDate > SomeTable.VisitDate
AND Temp.VisitDate < SomeTable.VisitDate +3)

Also, your logic doesn't appear to account for a weekends and 3
day-weekends - VisitDate on Friday that is completed on Tuesday.

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

 

[Cy]

Hmm, that would probably not work. Should not have been so quick to press
the send key.

SELECT JobCode, VisitDate
FROM SomeTable
WHERE Not Exists
(SELECT *
FROM SomeTable as Temp
WHERE Temp.JobCode = SomeTable.JobCode
AND Temp.VisitDate > SomeTable.VisitDate
AND Temp.VisitDate < SomeTable.VisitDate +3)

Also, your logic doesn't appear to account for a weekends and 3
day-weekends - VisitDate on Friday that is completed on Tuesday.

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

Thanks, this appears to do the trick. Run extremely slow, but does
work. Any suggestions on speeding it up?
Here's what I have:
SELECT EmployeeHoursEntry.JobName, EmployeeHoursEntry.ServiceNo,
EmployeeHoursEntry.Date, EmployeeHoursEntry.contractyear INTO HRLMTemp
FROM EmployeeHoursEntry
WHERE (((EmployeeHoursEntry.contractyear)="2007") AND ((Exists (SELECT
*
FROM EmployeeHoursEntry as Temp
WHERE Temp.ServiceNo = EmployeeHoursEntry.ServiceNo
AND Temp.Date > EmployeeHoursEntry.Date
AND Temp.Date < EmployeeHoursEntry.Date +3))=False));

 

[John Spencer]

You might try something like the following. I may have screwed up the
A.Date to B.Date comparisons. You might have to play with the join
criteria to get this to come out right.


SELECT EmployeeHoursEntry.JobName, EmployeeHoursEntry.ServiceNo,
EmployeeHoursEntry.Date, EmployeeHoursEntry.contractyear INTO HRLMTemp
FROM EmployeeHoursEntry As A LEFT JOIN
(SELECT *
FROM EmployeeHoursEntry
WHERE ContractYear = "2007") as B

ON A.ServiceNo = B.ServiceNo
AND A.Date < B.Date
AND A.Date > B.Date + 3

WHERE B.Date is Null



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