Previous And Next Records

[MadCatmk2]

Hi all

Using Access 97 Database with Crystal 9.

I am writing a report to calculate dependency of a patient.

The details section of my report consists of:

[Date_Of_Visit] [Time_Of_Visit] [Duration]

What i want to know is if the following is possible in the report:

For the current [date_of_visit] i want to check for the number of contacts in each of the seven day periods around the date.

e.g. if [Date_Of_Visit] = 10\10\2003 then

i need to count contacts between
4\10\2003 to 10\10\2003 and
5\10\2003 to 11\10\2003 and
.
.
.
10\10\2003 to 16\10\2003

I need to return the largest count from this group i.e. the period which has most [Date_Of_visit]'s


This needs to be done for each visit date.

Hopefully this makes sense to someone. I'm having difficulty in getting my head round how this could be done.

Any help at all is much appreciated.

Thanks in advance