Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Aggregating fields based on datetime month and day

Posted on 2013-06-06
1
Medium Priority
?
265 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 1120 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

971 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