• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

totals in query - by month

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
0
Pdeters
Asked:
Pdeters
  • 3
  • 2
1 Solution
 
ragoranCommented:
assuming Field1 type is date:

select format(Field1,"YYYYMM") as month, sum(Field2)
from table
group by format(Field1,"YYYYMM")
0
 
PdetersAuthor Commented:
Field 1 is date type. Whenyou say select format - do you mean in the properties
0
 
PdetersAuthor Commented:
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
0
 
ragoranCommented:
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"

then
Fields:   Month:format(Field1,"YYYYMM")        Field2
Table:   tablename                                         tableName
Operation:  Group By                                      sum
0
 
PdetersAuthor Commented:
Thank you - I was being dense. Works perfect. I was using DatePart for why I don't know.
Thank you again.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now