Solved

Query not summing values by year

Posted on 2015-01-27
2
111 Views
Last Modified: 2015-01-27
Why isn't this SQL in query designer summing the donations by family by year?

SELECT [Family Information].ID, Donations.[Date of Donation], Sum(Donations.[Donation Amount]) AS [SumOfDonation Amount]
FROM [Family Information] RIGHT JOIN Donations ON [Family Information].ID = Donations.[Family Name]
GROUP BY [Family Information].ID, Donations.[Date of Donation]
ORDER BY [Family Information].ID, Donations.[Date of Donation];
0
Comment
Question by:SteveL13
[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
2 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40572776
Because you are grouping on Date of Donation, each day is grouped. If you want to group by year, add the YEAR function to both the GROUP BY and the SELECT clause.
0
 
LVL 29

Expert Comment

by:fibo
ID: 40572808
Main source of problem: you are grouping not by year but by date (ie with resolution of teh second or less)

Suggested solution:
Not sure how to extract year from date in your own SQL dialect. With the examples at https://msdn.microsoft.com/en-us/library/ms174395.aspx  , your query could be something like
SELECT [Family Information].ID, Donations.[Date of Donation], 
DATENAME(year,  Donations.[Date of Donation]) AS DonYear,
Sum(Donations.[Donation Amount]) AS [SumOfDonation Amount]
FROM [Family Information] RIGHT JOIN Donations ON [Family Information].ID = Donations.[Family Name]
GROUP BY [Family Information].ID, DATENAME(year,  Donations.[Date of Donation])
ORDER BY [Family Information].ID, DATENAME(year,  Donations.[Date of Donation]) , Donations.[Date of Donation]; 

Open in new window

0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

751 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