Solved

Query not summing values by year

Posted on 2015-01-27
2
98 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
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now