Need Help Writing a Query

[MadCat]

Hi all

Hopefully someone can help me with this problem. I have a table in a database which shows patient contact details. From this table i have been trying to calculate a patients dependency based on the contacts made and there duration. I have written a VB program which will eventually do what i want but i am trying to speed things up by doing most of the work in queries.

My table is called [CONTACTS], the fields that are used within the table are PATIENT_NO, DATE_OF_VISIT, DURATION.

For each PATIENT_NO i need to check each of the DATE_OF_VISIT's and see how many other visits for that patient fall within a seven day period surrounding that date i.e.

if the patient has a visit date of 7/1/2004 then i need to check how many of the patients other visits fall in the periods:

1/1/2004-7/1/2004
2/1/2004-8/1/2004
3/1/2004-9/1/2004
.
.
.
7/1/2004-14/1/2004

from that i need to take the period with the most visits and use the count as a measure of dependency for the given date. e.g. if greater than 5 visits in a seven day period then dependency can be classed as high.

eventually i want to sum up the durations for the visits in each of the periods and use the maximum sum of that as well but the contact count is my main concern at present.

Hopefully this makes sense, please let me know if not.

Could anyone suggest if this is possible to do a query that can do this. If so suggest how it may be done.

Many thanks in advance

 

[WayneRyan]

Mad,

Make a new field on your form - Dependency.
Use the OnCurrent event of the form to:

Me.Dependency = DCount("[Patient_No]", _
                       "Contacts", _
                       "[Date_Of_Visit] between #" & Date - 7 & "# And #" & Date + 7 & "# And " & _
                       "[PATIENT_NO] = " & Me.Patient_No)

Which is really the same as the query:

Select Count(*) 
From   Contacts
Where  Patient_No = Forms![YourForm]![Patient_No] And
       Date_Of_Visit Between Date() - 7 And Date() + 7

Take your choice

Wayne

 

[MadCat]

Hi there

Thanks for the reply. Correct me if i'm wrong but the qury that you have posted counts all visits over a 14 day period around the date? Unfortunately this is not what i need it to do. I wish it was because it would be so much easier. I need a count for each of the seven day periods surrounding the current contact date, so that i can then take the largest value returned from the seven periods. Does this make sense?

Thanks again