Please Help Syntax Tweak (1 Viewer)

[ksaab]

I have a query that counts calls by date of call by user

I am trying to run an expression to sum the calls by week

So far I have something like:

Week 1: Sum(IIf([calldate] Between #7/11/2004# And #7/17/2004#,[CountOfcallid],0))

But it is not counting the calls correctly..could someone please help me tweak this syntax!

Thanks in advance,

 

[KenHigg]

What does the underlying table / flds look like?

You have a table where each record represents a call made and it has a date fld?

???
ken

 

[ksaab]

Yes, the table has a callid, callby, calldate, etc

I am trying to roll up the calls per day (callid by calldate) by weeks - to eventually yeild a QTD Avg in weeks.

Calculating the Avg Days worked per week is no problem. I just count each day that a user showed call activity and then group by Week:

Week 2: Count(IIf([calldate] Between #7/11/2004# And #7/17/2004#,"1",Null))

Then run: ([week 1]+[Week 2]+[Week 3])/3 to get the Avg by weekd to date.

When that same logic was applied to the Avg Calls per day by Weeks with similar syntax - I find it is summing the calldates not summing the countofcallid by calldate as I had hoped it would.

The syntax below:
Week 2: Sum(IIf([calldate] Between #7/11/2004# And #7/17/2004#,[CountOfcallid],0))

is the problem chld - and I can not work out the proper syntax.

Does that clarify? or confuse?

 

[KenHigg]

Sorry for dragging this out...

You want to know the avg number of calls per week for a given day range?

kh

 

[ksaab]

Yes that is basically what I am trying to do. I would like to find a more graceful way to approach it since at this point I have to add a new Week# to two queries each time I do this..

Thanks!

 

[ecniv]

Had to run it through two sub queries (including a cartesian :/ )

First one totalled the calls per day (count)
Second one put week numbers to the day and calls (cartesian)
Third either summed the week numbers and calls or averaged them (avg function?)


Vince

 

[KenHigg]

Hum...

To start, you could put your date range for records in the criteria section of the query builder and make the query a totals query. Something like 'Between [Start date] And [Ending Date]'. But don't display the col.

Then in the next col, something like:

WeekNum: DatePart("w", me!calldate)

This will give you a value of 1 - 54, for the number of weeks in the year. Group on this col.

Do a third col. like:

WeekNum: DatePart("w", me!calldate)

But do a avg on this col.

I think this will work.


???
kh

 

[ksaab]

I will give that a shot. I think I may have stumbled apon a solution. I created a SQL View with to eliminate some subqueries and reflect an accurate count of call days per user per week. When I roll that up in a similar query it seems to work out.

KenHigg - I am going to try your solution as well. It sounds like it may get me from having to maintain a separate view for this function. Thanks!

As far as the caretesian that was mentioned .. I misspoke when I said that the queries were built from a table they are bulit on other count queries..sorry about that. I get so used to complexity that eventually it seems simple to me.

 

[The_Doc_Man]

Should be able to do everything all at once (except filtering) by using a crosstab query.

Look it up in Help. They do MARVELOUS things. Including sums, averages, and standard deviations and min/max type stuff, too.