How do I Group By Month?

[kjsturm]

I have a query that is prompting the user to enter a beginning date and ending date. How do I modify the SQL statement below to group by Month rather than grouping by date? Example: If the user enters a starting date of 1/1/07 and and ending date of 5/30/07 it should group by January, February, March, April, May, etc. (or 01/07; 02/07; 03/07; etc.) Here is my SQL:

SELECT claims.RECEIVED_DATE, claims.OSC_ID, claims.MARKET, Sum(claims.TOTAL_CLAIMS) AS SumOfTOTAL_CLAIMS
FROM claims
GROUP BY claims.RECEIVED_DATE, claims.OSC_ID, claims.MARKET
HAVING (((claims.RECEIVED_DATE) Between [Enter Beginning Date] And [Enter Ending Date]) AND ((claims.MARKET)=[enter market]))
ORDER BY claims.RECEIVED_DATE, claims.OSC_ID, claims.MARKET;

 

[lespaul]

a starting point:

SELECT [b]MONTH(claims.RECEIVED_DATE)[/b], claims.OSC_ID, claims.MARKET, Sum(claims.TOTAL_CLAIMS) AS SumOfTOTAL_CLAIMS
FROM claims
WHERE (((claims.RECEIVED_DATE) Between [Enter Beginning  Date] And [Enter Ending Date]) AND ((claims.MARKET)=[enter market]))
GROUP BY MONTH(claims.RECEIVED_DATE), claims.OSC_ID, claims.MARKET
ORDER BY claims.RECEIVED_DATE, claims.OSC_ID, claims.MARKET;

additionally Access uses the HAVING clause incorrectly. It automatically uses it when there's an aggregate function in the SELECT clause, but really, you only need it if you are filtering on the aggregate. For instance, if you only wanted to find where the sum of Total_Claims is greater than 10000, THEN you would use a HAVING clause:

SELECT MONTH(claims.RECEIVED_DATE), claims.OSC_ID, claims.MARKET, Sum(claims.TOTAL_CLAIMS) AS SumOfTOTAL_CLAIMS
FROM claims
WHERE (((claims.RECEIVED_DATE) Between [Enter Beginning  Date] And [Enter Ending Date]) AND ((claims.MARKET)=[enter market]))
GROUP BY MONTH(claims.RECEIVED_DATE), claims.OSC_ID, claims.MARKET
[b]HAVING Sum(claims.TOTAL_CLAIMS) > 10000[/b]
ORDER BY claims.RECEIVED_DATE, claims.OSC_ID, claims.MARKET;

Leslie

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

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

What about something like this ?

SELECT Format([RECEIVED_DATE],'yyyy-mm') As [Month], OSC_ID, MARKET, Sum(TOTAL_CLAIMS) AS SumOfTOTAL_CLAIMS
FROM claims
WHERE (RECEIVED_DATE Between [Enter Beginning  Date] And [Enter Ending Date])
 AND MARKET=[enter market]
GROUP BY Format([RECEIVED_DATE],'yyyy-mm'), OSC_ID, MARKET
ORDER BY 1, 2, 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjsturm]

Thanks!

One last question - anyway I can format that into weeks? or format "1" to "JAN" "2" to "FEB" etc?

 

[kjsturm]

Thanks Leslie and PH! PH - That formatting worked great. I think my boss will probably ask for it to be broken down into weeks - but I'm keeping my fingers crossed that monthly will suffice.