CODEWITHSUNDEEP
sqlms-accessms-access-2010
Drew Chapin
Drew Chapin

Reputation: 7989

Year-To-Date Summary by Month in Access SQL

Given this table, I need to generate a year-to-date summary by month. The result should have one row for each month of the year, and a running total for the year up to that month. I am by no means a novice when it comes to SQL, yet I still have no idea how to achieve this in pure SQL. Does anyone know how to do this? Please note that it needs to be compatible with Microsoft Access SQL. 

projectTitle  | completionDate | amount
---------------------------------------
'Project #1'  | 2013-01-12     | 1234
'Project #2'  | 2013-01-25     | 4567
'Project #3'  | 2013-02-08     | 8901
'Project #4'  | 2013-02-15     | 2345
'Project #5'  | 2013-02-20     | 6789
'Project #6'  | 2013-03-01     | 1234
'Project #7'  | 2013-04-12     | 5678
'Project #8'  | 2013-05-06     | 9012
'Project #9'  | 2013-05-20     | 3456
'Project #10' | 2013-06-18     | 7890
'Project #11' | 2013-08-10     | 1234

Example of the expected results

month    | amount
-----------------
'Jan-13' | 5801          -- = Project #1 + Project #2 + Project #3
'Feb-13' | 23836         -- = Project #1 + Project #2 + Project #3 + Project #4 + Project #5
'Mar-13' | 25070         -- ...and so on
'Apr-13' | 30748         
'May-13' | 43216         
'Jun-13' | 51106
'Jul-13' | 51106          
'Aug-13' | 52340

In my example output, you may notice I included July even though the example table did not have any data for July. Ideally, the output would still include July as you see in the example output, however I can live without if necessary.

Upvotes: 0

Views: 1756

Answers (3)

Drew Chapin
Drew Chapin

Reputation: 7989

I found the answer by using a sub-query, and using aliases for the table name. 

SELECT FORMAT(t1.completionDate,"mmm-yy") AS [month], (
        SELECT SUM(t2.amount)
        FROM projects AS t2
        WHERE MONTH(t2.completionDate) <= MONTH(t1.completionDate)
    ) AS [amount]
FROM projects AS t1;

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

Select Month(CompletionDate) as MthComplete, Year(CompletionDate) as YrComplete, AMT as Sum(Amount)
From MyTable
Group By Year(CompletionDate), Month(CompletionDate)
Order By Year(CompletionDate), Month(CompletionDate)

You'll probably have to putz with another column to get the Year/Month format you're using in your output example, but that will group the data the way you want it.

Upvotes: 0

Sarajog
Sarajog

Reputation: 164

I think, trying to group it by Month could help. Look here to see how to Group it by Month. Then you should be able to sum the amount an give it out by the Month.

Hope this Idea helps you.

Sarajog

Upvotes: 0

Related Questions