average

I

Ian

forgot the sql statment

SELECT Count(dbo_LU_ApptType.ApptTypeDescr) AS CountOfApptTypeDescr,
dbo_Appt.ApptDate
FROM dbo_Appt INNER JOIN dbo_LU_ApptType ON dbo_Appt.ApptTypeID =
dbo_LU_ApptType.ApptTypeID
GROUP BY dbo_Appt.ApptDate
HAVING (((dbo_Appt.ApptDate) Between (Date()-90) And Date()));
 
P

Pieter Wijnen

& to post in the same thread ...

not knowing what the Q is, I'll give you bit of SQL advice instead <g>
You should change the Having clause to a where clause for speed purposes.

ie
SELECT Count(dbo_LU_ApptType.ApptTypeDescr) AS CountOfApptTypeDescr,
dbo_Appt.ApptDate
FROM dbo_Appt INNER JOIN dbo_LU_ApptType ON dbo_Appt.ApptTypeID =
dbo_LU_ApptType.ApptTypeID
WHERE dbo_Appt.ApptDate Between Date()-90 And Date()
GROUP BY dbo_Appt.ApptDate

Pieter
 
I

Ian

sorry Peter -- here is the question from the original thread

I am trying to find average number of appointments in a week in query
The following SQL gives me the count of appointments on a given day. Is
there any way to organize this within a query per now show me the average
over 7 days? I'm going to need the number for in another equation so I don't
want to do it within a pivot. Thanks Ian.
 
P

Pieter Wijnen

Build a new query based on the count query
something like:
Select Avg(CountField) As WeekAvg, Year(DateField) & '-' &
Format(DateField,"ww") As YearWeek
From CountQuery
Group By Year(DateField) & '-' & Format(DateField,"ww")

HtH

Pieter
 
Top