CODEWITHSUNDEEP
sqlms-access
R.Ames
R.Ames

Reputation: 49

combining dates when doing a sum query

I would like to sum values over time. The difficulty that i am having with this is if there is a way to assign a date to the end value. To clarify, here is my example table.

Object  Observation  Date 
   1       215       10/1/2015
   2       125       10/1/2015
   1       225       10/4/2015
   2       150       10/4/2015
   1       250       10/8/2015

The idea is to sum the observation by Object and Date. This is easy, the hard part is something I'm not sure how i would do. Currently I sum by month using this query

SELECT Object, Sum(Observation) AS [Total], Month([Date]) AS [Month], 
FROM Records
GROUP BY Month([Date]), Object;

This gives my the total for the month. Ideally what I would like to do though is to make it so that rather than getting a numeric month, mm, I get a date mm/dd/yyyy. So the Date post sum would show up as 10/01/2015. So that output would look something like this:

Object   Total    Date
   1      690    10/1/2015
   2      275    10/1/2015

I have build out a query that uses a calendar to select a start and end date for pulling multiple months at a time that I might want to compare.

SELECT Object, Date, Total
FROM Table1
WHERE [Month] Between Month(Forms![Main Form]![Start Date]) And Month(Forms![Main Form]![End Date])
GROUP BY Object, Month, Total;

My current method doesn't allow for me to roll over into the next calendar year. The hope is that this would fix my issue with the roll over issue. Is this possible to assign the first of the month to the date after the month is summed?

Upvotes: 1

Views: 100

Answers (2)

Gustav
Gustav

Reputation: 55831

That could be:

SELECT 
    Object, 
    DateSerial(Year([Date]), Month([Date]), 1) As YearMonth, 
    Sum(Observation) AS [Total]
FROM 
    Table1
WHERE 
    [Date] 
        Between Forms![Main Form]![Start Date] 
        And Forms![Main Form]![End Date]
GROUP BY 
    Object, 
    DateSerial(Year([Date]), Month([Date]), 1)

Upvotes: 1

Morpheus
Morpheus

Reputation: 1634

You can use DateSerial() to determine the first day of the month.

SELECT Object, Sum(Observation) AS [Total], DateSerial(Year(Date()), Month(Date()),1) AS [Month], 
FROM Records
GROUP BY DateSerial(Year(Date()), Month(Date()),1), Object;

Upvotes: 0

Related Questions