Calculation in SQL (1 Viewer)

[vbaInet]

Re: Calulation in SQL

Alright moishy.

1. Create a query to calculate just the Average as you want it.
2. Join it to the query that you already have. You can remove the Round(Avg()) part from the original query in your other post.

 

[moishy]

Re: Calulation in SQL

1. How can I get the query to also show records that don't have an average (due to the criteria used)?
2. Why is the query only returning the first donation per donor?

 

[vbaInet]

1. What do you mean? Are you talking about Nulls?
2. I suspect the grouping but I will need to see the db to give you an explanation. But in the meantime, just follow what I advised in my last post.

 

[moishy]

1. I have the qryAverage calculating the average only for donations given more than 3 year ago, so new donors don't have average. Now what's happening is the query (qryDonations) only returns the records that have an average.

2. I did what advised in your last post, and I still have the same problem. Attached please find the sample db.


Once again thank you so much for your time and help.

 

[student]

Having looked at the query, you have set it to only return results for where the table entries are dated 3 or more years ago. Your WHERE clause excludes records more recent than 3 years prior to the current date.

 

[vbaInet]

Having looked at the query, you have set it to only return results for where the table entries are dated 3 or more years ago. Your WHERE clause excludes records more recent than 3 years prior to the current date.

student is on the right track. Your Average query should be this:

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

Note the operators used.

 

[moishy]

Thanks folks, I really appreciate your help.
One last problem (I hope) I have with the query is it returns a new line for each donation, I was hoping I could get one line per-donor.

 

[vbaInet]

Don't include Donations in the fields list in your query if you want one record per donor.

 

[moishy]

This is the SQL I'm using, it returns a line per-donation

SELECT tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], 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], qryAverage.Average
FROM (tblDonorDetails INNER JOIN qryAverage ON tblDonorDetails.ID = qryAverage.ID) 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, qryAverage.Average;

 

[vbaInet]

And what do you want me to do with the SQL you provided? I've just told you what you need to do.

 

[moishy]

I don't get it, since I have a column for each year I want one line per donor and all 4 columns should show the donation for that year.

 

[moishy]

I don't understand, since the query has 4 columns (one for each year) I want one line per-donor (all columns should be filled ("" where no record)). Is this possible and if so can you please elaborate.
Thanks again.

 

[vbaInet]

Let's go back a few threads. I asked you, "what is the Record Source of your report?" and you said?

I just want a simple answer.

 

[moishy]

I guess I didn't understand.
I have 4 columns (one for each year) is not possible to make the query show 1 line per donor and have all 4 columns in the same row?

 

[vbaInet]

I guess I didn't understand.
I have 4 columns (one for each year) is not possible to make the query show 1 line per donor and have all 4 columns in the same row?

Is that a reply to my last question or perhaps you missed my post?

 

[moishy]

Sorry, I missed your post. The SQL is changing based upon your suggestions. in a previous post I posted the current SQL. here it is:

SELECT tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], 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], qryAverage.Average
FROM (tblDonorDetails INNER JOIN qryAverage ON tblDonorDetails.ID = qryAverage.ID) 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, qryAverage.Average;

 

[vbaInet]

Is there still a problem here?

 

[moishy]

Yes, Thanks for your attention time patience and help.
The problem I'm having is that I want all 4 columns (one for each year) to show up in 1 line (per donor and have all 4 columns in the same row).

 

[vbaInet]

Can you upload a database I can use? Tell me the query I should look at and also upload a spreadsheet of what you would want it to be.

 

[moishy]

vbaInet

You're amazing.

Attached please find a sample mdb, the query I'm struggling with is qryDonations.
I also attached a spreadsheet of what I would like the query to look like.

Thanks once again.