Access SQl Query

[chip12]

I need to group my data by month. The query I run gives the average of a select column of numbers but groups by days instead of months Below is my results. I need to group the averages by month instead of days. My code is simply not working Thanks in advance

6.762790698 1/2/2004
6.708717949 1/5/2004
6.72 1/6/2004
6.690588235 1/7/2004

 

[PHV]

My code is simply not working
Which code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chip12]

The code is Select avg(turn) from (tablename) group by funding_date. The averages are base on the individual day. I need the averages to be based on a month by month basis.
Thanks again

 

[PHV]

Why not simply this ?
SELECT Avg(turn) As Average, Format(funding_date, 'yyyy-mm') As [Month]
FROM tablename
GROUP BY Format(funding_date, 'yyyy-mm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chip12]

Thank you very much. it worked

 

[Dibblet]

I had a similar problem. An alternative you could try using would be -

Group by month(funding_date)

Since its solved its probably not important though.

 

[PHV]

Dibblet, and what happens if funding_date spans several years ?

 

[Dibblet]

I'll tell you in several years I only implemented it a couple of weeks ago!

 

[lespaul]

what will happen is that all the January information will be averaged no matter the year. So the Jan 2001, 2002, 2003, 2004 and 2005 information will be in a single record - January. If you want to break down the average of January BY YEAR, you would need to use PHV's solution.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design