Line per Column

[moishy]

Hello folks,

I have a mbd used to track donations made to our organization, I created a query (with the gracious help of vbaInet) that has in addition to the donor's info another four columns to show the donations for the past 3 years + the current year, the trouble I'm experiencing is that the query returns a separate line per-year.
Since 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?
Attached please find the SQL I'm using.

Thanks in advance!

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;

 

[Privateer]

Mioshy,
I have run into the same issue with crosstab queries and my solution was a bit extreme, but I could not think of another way. I created a temporary table where I put, (in your case) the DonerDetailsID and their names, with a current year donations field CYD, a prior year one PYD1 field, PYD2 field, PYD3 field and I ran two for loops. I created a recordset of the temp table and looped through it grabbing the ID, concatenating it with the current year for the criteria, finding that record in a sum query also as a recordset and updating the CYD field in the temp table with the totals. Then I subtracted a year and looped through all the IDs again with the current year minus one and put those totals into PYD1. I repeated this outer loop four times to get all four years. This temp table was the source for my report. Describing the code through this medium is not possible, but if you really need this I can paste a good example here. But it is really advanced. It involves recordsets, for loops and other code that might be more trouble for you than it is worth. Let me know if you want to take a stab at it.
Privateer

 

[stopher]

I have a mbd used to track donations made to our organization, I created a query (with the gracious help of vbaInet) that has in addition to the donor's info another four columns to show the donations for the past 3 years + the current year, the trouble I'm experiencing is that the query returns a separate line per-year.
Since 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?
Attached please find the SQL I'm using.

You seem to be trying to create the effect of a crosstab query without actually using a crosstab.

Try this query:

TRANSFORM Sum(tblDonations.Amount) AS SumOfAmount
SELECT tblDonorDetails.[First Name], tblDonorDetails.Surname
FROM (tblDonorDetails INNER JOIN qryAverage ON tblDonorDetails.ID = qryAverage.ID) INNER JOIN tblDonations ON tblDonorDetails.ID = tblDonations.ID
WHERE (((tblDonations.CivilYear)>=Year(Date())-"3"))
GROUP BY tblDonorDetails.ID, tblDonorDetails.Surname, tblDonorDetails.[First Name], tblDonorDetails.Surname
PIVOT IIf([CivilYear]=Year(Date()),"This Year",IIf([CivilYear]=Year(Date())-1,"Last Year",IIf([CivilYear]=Year(Date())-2,"Two Years Ago",IIf([CivilYear]=Year(Date())-3,"Three Years Ago",0))));

The most interesting line is the last line that works out the year expression. The PIVOT bit then uses this as the column. It's actually easier to see when you look at it in design view.

If that helps then we can look at adding qryAverage and other stuff quite easily.

hth
Chris

 

[moishy]

Hey folks I appreciate your input.

Privateer, I'd like to see a sample if that's OK with you.

stopher (Chris), the query you posted almost does the job, the trouble is that if a donor doesn't have record for each the columns (years) that donor won't show up. btw how can I add other fields to the query.

Thank you both