Tough Query Question

[tma12000]

I am trying to create a output field in a query that takes the sume of a field called "spend" and divides it by the count that same field, thus giving me an average. Here is the code Ive tried that has not worked:

Avg Monthly Spend: (Sum([Expense Amount]))\(Count([Expense Amount]))

I get an "You cannot have aggragate functions in a group by clause"

Any thoughts,
Thanks,
Tim

 

[Blorf]

This may seem overly simple, but couldn't you just get rid of the sum and count and just use avg?

Like this
SELECT Avg([expense amount]) AS expense_amount FROM Your_Table;

ChaZ

 

[tma12000]

Heh heh thanks Chaz, I thought about that.Unfortuanley I am looking at expense line item data so I have several lines per person per month. When I put the date filter on (to just look at Jan for instance) it outputs every expense list for that month and thus gives me an avg per line item. So I need to 1st do a count on the month, then a sum then do the math.

Thanks,
Tim

 

[tma12000]

To add...the expense date format is dd/mm/yy... Is there an easy way to remove the day from that format. This is the crux of my problem.

 

[Blorf]

Ok, this may help

get rid of the date, and use the following

Format ([Date], "YYYY-MM&quot

This will give you the year first, then the month.

So 1/15/03 will be 0003-01.

I like to use the four digit year, and put it before month, because if you ever need to sort, this will be sortable.

Hope it helps.

 

[tma12000]

Thanks so much, that did it!