Solved

Aggregating fields based on datetime month and day

Posted on 2013-06-06
1
236 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

707 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

20 Experts available now in Live!

Get 1:1 Help Now