Calculation in SQL (1 Viewer)

[moishy]

Hello Folks,

Our organization has a mdb used to track all donations. I've been trying to put a query together that will show all donors and their donations for the past three years (the years cannot be hard coded because it will be used for years to come).

The mdb structure is as following:

tblDonors includes the following fields:
ID (PK)
Surname
First Name
City
Country

tblDonations includes the following fields:
ID
Amount
CivilMonth
CivilYear

The query I'm trying to make should show all info from tblDonors, and in addition three more columns, each for one of the last three years.

 

[vbaInet]

Re: Calulation in SQL

moishy,

Isn't this what was being discussed in this thread?

http://www.access-programmers.co.uk/forums/showthread.php?t=219380

 

[moishy]

Re: Calulation in SQL

Yes it is, sorry I didn't mention it, I felt my question was not understood the way I had intended, so I rearranged the question here.
btw I meant to put a link to this post in my last response in that post.

 

[vbaInet]

Re: Calulation in SQL

If you felt that your question was misunderstood you should have simply clarified the situation and not post a new thread. Posting a new thread would not necessarily attract a fresh pair of eyes.

There was one more step left and I clearly stated in post #12 that before I give you the final piece of the puzzle, I needed something clarified.

 

[moishy]

Re: Calulation in SQL

Theoretically you're right, null and "" should show the same thing, but I once saw a query that worked that used the conversion.

Thanks again.
btw I really do appreciate the way you teach the solution and not only give the answer.

 

[vbaInet]

Re: Calulation in SQL

It's just double work converting Null to "". It helps even more if the field's Allow Zero-Length String property is set to No.

For the This Year field, you would use:

SELECT IIF([COLOR=Blue]CivilYear = Year(Date())-1[/COLOR], Amount & Currency, Null) AS [This Year], ...

 

[moishy]

Re: Calulation in SQL

Thank you so much. It finally worked. here is the SQL that I use now, If you have any comments I'd be glad to read them

SELECT DISTINCT tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonations.Amount, tblDonations.Currency, tblDonations.CivilYear, tblDonations.Month, IIf([CivilYear]=Year(Date()),[Amount] & " " & [Currency],Null) AS [This Year], IIf([CivilYear]=Year(Date())-1,[Amount] & " " & [Currency],Null) AS [Last Year], IIf([CivilYear]=Year(Date())-2,[Amount] & " " & [Currency],Null) AS [Two Years Ago], IIf([CivilYear]=Year(Date())-3,[Amount] & " " & [Currency],Null) AS [Three Years Ago]
FROM tblDonations, tblDonorDetails;

 

[moishy]

Re: Calulation in SQL

How can I add a field which calculates the average for the last 3 years for each donor?

 

[moishy]

Re: Calulation in SQL

I thought something like this should work but it doesn't.

SELECT DISTINCTROW tblDonations.[ID], Round(Avg(tblDonations.Amount)) AS Average
FROM tblDonations
WHERE (((tblDonations.CivilYear)<Year(Date())-2))
GROUP BY tblDonations.[ID];

 

[vbaInet]

Re: Calulation in SQL

Thank you so much. It finally worked. here is the SQL that I use now, If you have any comments I'd be glad to read them

Yes, like I mentioned in the other thread, you need to put the WHERE clause too. I explained it all in your original thread.

 

[vbaInet]

Re: Calulation in SQL

I thought something like this should work but it doesn't.

Shouldn't this be asked in a new thread?

By the way, I don't see anything wrong with the SQL statement.

 

[moishy]

Re: Calulation in SQL

Yes, like I mentioned in the other thread, you need to put the WHERE clause too. I explained it all in your original thread.

Ok I forgot that it's included now

 

[vbaInet]

Re: Calulation in SQL

Good stuff

 

[moishy]

Re: Calulation in SQL

Shouldn't this be asked in a new thread?
By the way, I don't see anything wrong with the SQL statement.

It could be, the reason I didn't post a new thread is because although it works on its own when I try to add it to the query dealt with in this thread all I get is a blank query.
If you still think I shall post the question in a new thread I will do so.

Also, another issue I've been having with this query is that it only shows the first donation by each donor instead of showing all donations for all donors from the last three years.

Your most gracious help is much appreciated.

 

[vbaInet]

Re: Calulation in SQL

You will need to tweak a few things

SELECT [COLOR=Blue]Round(Avg(tblDonations.Amount)) AS Average[/COLOR], tblDonorDetails.ID,  tblDonorDetails.Surname, tblDonorDetails.[First Name],  tblDonations.Amount, tblDonations.Currency, tblDonations.CivilYear,  tblDonations.Month, IIf([CivilYear]=Year(Date()),[Amount] & " "  & [Currency],Null) AS [This Year],  IIf([CivilYear]=Year(Date())-1,[Amount] & " " & [Currency],Null)  AS [Last Year], IIf([CivilYear]=Year(Date())-2,[Amount] & " " &  [Currency],Null) AS [Two Years Ago],  IIf([CivilYear]=Year(Date())-3,[Amount] & " " & [Currency],Null)  AS [Three Years Ago]
FROM [COLOR=Blue]tblDonations INNER JOIN tblDonorDetails ON tblDonations.ID = tblDonorDetails.ID[/COLOR]
WHERE ((tblDonations.CivilYear = Year(Date())-1) OR  (tblDonations.CivilYear = Year(Date())-2) OR (tblDonations.CivilYear =  Year(Date())-3));
GROUP BY [COLOR=Blue]... all the necessary fields here ...[/COLOR];

 

[moishy]

Re: Calulation in SQL

I implemented your changes but since I'm having the following problem as mentioned above

Also, another issue I've been having with this query is that it only shows the first donation by each donor instead of showing all donations for all donors from the last three years.

it calculates the average only for the first donation, which in fact is not an average at all!

 

[vbaInet]

Re: Calulation in SQL

So what's the SQL statement?

 

[moishy]

Re: Calulation in SQL

Here it is:

SELECT Round(Avg(tblDonations.Amount)) AS Average, tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonations.Amount, tblDonations.Currency, tblDonations.CivilYear, tblDonations.Month, IIf([CivilYear]=Year(Date()),[Amount] & " " & [Currency],Null) AS [This Year], IIf([CivilYear]=Year(Date())-1,[Amount] & " " & [Currency],Null) AS [Last Year], IIf([CivilYear]=Year(Date())-2,[Amount] & " " & [Currency],Null) AS [Two Years Ago], IIf([CivilYear]=Year(Date())-3,[Amount] & " " & [Currency],Null) AS [Three Years Ago]
FROM tblDonorDetails INNER JOIN tblDonations ON tblDonorDetails.ID = tblDonations.ID
WHERE (((tblDonations.CivilYear)=Year(Date())-1 Or (tblDonations.CivilYear)=Year(Date())-2 Or (tblDonations.CivilYear)=Year(Date())-3))
GROUP BY tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonations.Amount, tblDonations.Currency, tblDonations.CivilYear, tblDonations.Month;

 

[vbaInet]

Re: Calulation in SQL

It is definitely calculating the Average but you may think it's not an average because the value is the same as the first record. Perform a manual calculation yourself and see if it matches.

 

[moishy]

Re: Calulation in SQL

The query only takes in account (displays) the first donation for each donor, and manual calculation proves that the query isn't calculating the average of the past 3 years (100 in 2008 and 580 in 2010, returns an average of 100!).