How to sum up dates in a query???

[Cherish2007]

Hello, i am trying to sum up dates in the query and i am having a bit of difficulty with it. I have Sales date from March 1, 2006 to March 31, 2006 and i need to come up with a sum. When i do the sum function in the Sales column and run the query i get all the dates from March 1 - 31. Is it possible to just get one line which sums all the Sales numbers together? What's the criteria for that? Thank you!

 

[PHV]

To get a sum by month (SQL code):
SELECT Format([date field], "yyyy-mm") AS [Month], Sum([sales field]) AS Total
FROM yourTable
GROUP BY Format([date field], "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

 

[Cherish2007]

Thank you! So there is no way to do it without writing the code?

 

[lespaul]

you can copy & paste the SQL that PHV provided, correct the field names and run it....

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]

So there is no way to do it without writing the code
I personally don't use the query grid as I don't like the messy (when not buggy) SQL code generated ...

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