Solved

Aggregating fields based on datetime month and day

Posted on 2013-06-06
1
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

615 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