Solved

Aggregating fields based on datetime month and day

Posted on 2013-06-06
1
247 Views
Last Modified: 2013-06-06
Dear all,

I would like to create a monthly and daily queries to aggregate fields, i.e. buildingcost, numberMonths for buildDateTime.

i.e. Montly Aggregate

buildDateTime            buildingcost
01/01/2004 00:00:00  9
02/01/2004 00:00:00  10

Returning:

buildDateTime  buildingcost
01/2004             19

etc.

I've tried the following without success:

SELECT table1.buildDateTime, SUM(table1.buildingcost), SUM(table1.numberMonths
FROM buildDateTime
WHERE (table1.buildDateTime>= #01/01/2002 00:00:00# AND table1.buildDateTime< #01/12/2002 00:00:00#)
GROUP BY table1.buildDateTime; // how do I specify the month and year

Open in new window


Thanks
0
Comment
Question by:AndyC1000
1 Comment
 
LVL 77

Accepted Solution

by:
peter57r earned 280 total points
ID: 39224874
SELECT format(buildDateTime, "yyyy/mm") as Period, SUM(buildingcost) as TotCost

FROM buildDateTime

GROUP BY format(buildDateTime, "yyyy/mm")
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

792 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