summing last three consecutive months

[Andre C]

I have a query from multiple tables which records visits to patients,
hence a patient can have more than one visit per month or infact none.
I want to produce ultimately a report but I guess a query first which
will sum up all visits to the patients over the last three months.
From this I will calculate which patients are highly dependent.

I can produce a query and a crosstab querry which counts the visits
per month but I juust want to count the vbisits over three months.

Fields I have which I think are relevent are
visitdate
ICSnumber (which is patientID)
also in query I have seperated visitmonth and visit year.

Any ideas. I have tried datediff with no sucess to date.

 

[John Vinson]

I have a query from multiple tables which records visits to patients,
hence a patient can have more than one visit per month or infact none.
I want to produce ultimately a report but I guess a query first which
will sum up all visits to the patients over the last three months.
From this I will calculate which patients are highly dependent.

I can produce a query and a crosstab querry which counts the visits
per month but I juust want to count the vbisits over three months.

Fields I have which I think are relevent are
visitdate
ICSnumber (which is patientID)
also in query I have seperated visitmonth and visit year.

Any ideas. I have tried datediff with no sucess to date.

Use a criterion on visitdate of

BETWEEN DateAdd("m", -3, Date()) AND Date()

In the Crosstab query make sure this uses the WHERE "aggregate"
function.

John W. Vinson[MVP]

 

[Chris]

Hi,

I think you just need to make a totals query. Create a query, select
ICSNumber and visitdate and visitdate again. Click the totals button. You
want to "group by" ICSNumbe, "Count" visitdate, and choose "where" for the
second visitdate. Also, deselect the display for the second visitdate.

You will have to build a criteria for the 2nd visitdate as well. Something
like ">= dateadd("m", -3, now())". I'm not exactly sure if this syntax is
correct, but you are subtracting 3 months from today's date and checking that
the visitdate is greater than or equal to that date.

Hope this helps.

 

[Andre C]

Use a criterion on visitdate of

BETWEEN DateAdd("m", -3, Date()) AND Date()

In the Crosstab query make sure this uses the WHERE "aggregate"
function.

Thanks but it wont let me do that. My sql code looks like this

TRANSFORM Count(basicvisitquery.VISITid) AS CountOfVISITid
SELECT basicvisitquery.ICSNumberID, [basicvisitquery]![FirstName]+"
"+[basicvisitquery]![Contacts.LastName] AS fullname,
basicvisitquery.visityear, Count(basicvisitquery.VISITid) AS [total
visits]
FROM basicvisitquery
GROUP BY basicvisitquery.ICSNumberID, [basicvisitquery]![FirstName]+"
"+[basicvisitquery]![Contacts.LastName], basicvisitquery.visityear
ORDER BY basicvisitquery.visityear
PIVOT "Mth" & DateDiff("m",[Visitdate],Date()) In
("Mth0","Mth1","Mth2");

 

[google]

Hi,

I think you just need to make a totals query.

Thanks that seems to work.