Average number issues active per day each month

[bcapps]

I am having trouble with a SQL select statement. I have an Access database with a start date and a stop date of an event. I want to know what the average number of events are active each day, each month over the last year. So far my querry is giving me a synatx error. I need help!

Here's the part of the code I'm have trouble with:

objNum = -11
Do Until objNum = 1
SELECT ((COUNT(eventID) * (SUM(DateDiff('d', IIF(StartDate < Month(DateAdd('m', objNum, Date()))'/1/'YEAR(DateAdd('m', objNum, Date())), Month(DateAdd('m', objNum, Date()))'/1/'YEAR(DateAdd('m', objNum, Date())), StartDate), IIF(IsNull(EndDate, Date(), EndDate)))))/30) AS AvgOpenEvents FROM tblEvents&quot;

Basically here is what I am saying: If the selected event started before the first of the month, set the date to the first, else keep the date of the event, give me the difference between that and the end of the event in number of days, That gives me total number of days for an event, give me the sum of days for all events, and divide that by 30. That gives me the average number of events active each day of the month. Later I will determine the number of days in a month to be more precise, but for now I just need to get past the first part.

Thanks
bc

 

[jhall156]

I haven't checked your logic but you need a group by clause if you want to use domain aggregate functions.

 

[bcapps]

Was out the last two days,
Could you please provide an example of how to do this?
Thanks
bc

 

[jhall156]

Try adding
GROUP BY eventID ;

I started out with nothing, and I still have most of it.

 

[bcapps]

ok,
I use a where clause to gather many events based on the month an issue is created, which works for gathering the number of events created each month, the number of events ending each month, and I am able to gather the average number of weeks an event goes on once it is closed. I am unfamiliar with this Group By so I am having a hard time finding an example of what it would look like in the code. The EventID is a unique number that can't be grouped.

 

[jhall156]

Sorry, I misunderstood the problem. Give me a few. I started out with nothing, and I still have most of it.