totals in query - by month

Posted on 2007-10-18
Last Modified: 2010-03-20
I have an Access 2003 query. In the query I have 3 fields. One of the fiels i Want to sum but only sum by months. Example

FIeld 1           Field 2
May 1, 2007
May 3, 2007
May 15, 2007

Field 2 is a $ amount.
 I can get all the May1 to totla and all the May 3 to total but I want all May to total and all june to total
Question by:Pdeters
    LVL 14

    Expert Comment

    assuming Field1 type is date:

    select format(Field1,"YYYYMM") as month, sum(Field2)
    from table
    group by format(Field1,"YYYYMM")

    Author Comment

    Field 1 is date type. Whenyou say select format - do you mean in the properties

    Author Comment

    I have changed the properites when i have the properties openin the query for that column - it shows it MM YYYY but does not sum them that way - still keesp them apart

    May 07
    May 07
    May 07

    they are all different May dates just shwo this way
    LVL 14

    Accepted Solution

    No, what I type is an SQL instruction.  If you are using the query builder:

    Bring the table in the editor windows

    set the "Group by option"

    Fields:   Month:format(Field1,"YYYYMM")        Field2
    Table:   tablename                                         tableName
    Operation:  Group By                                      sum

    Author Comment

    Thank you - I was being dense. Works perfect. I was using DatePart for why I don't know.
    Thank you again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now