TOP X aggregated

[rkaupp]

Hi all,

I have a bit of an issue for which I suspect there could be an easy solution, but so far it defies my logic. I work in a charity, and I have a table to record donations from funders to projects, with the following fields:

• TransactionID (autonumber - primary key)
• Project
• Funder
• TransactionDate
• Amount

I would like (as part of a wider query) to identify the three main funders for each project, by decreasing order of donations; I would also like to have the three funders in one field (saying: "Funder 1, Funder 2, Funder 3") so that I avoid having three lines for each project.

So far I have an aggregate query as follows:

SELECT Project, Funder, Sum(Amount) AS TotalIncome
FROM Table
GROUP BY Project, Funder
ORDER BY Sum(amount) DESC

This returns all funders by decreasing order. Now, if I want to have the main funders, I can use allen browne's tip (http://allenbrowne.com/subquery-01.html#TopN) but this will return one row per funder; I would like to have one row with the three funders.

Any suggestion on how to go about it?

Thank you,
Rémi

 

[Beetle]

Well, since you're already familiar with Allen Browne, have a look at his Concatenate function.

 

[rkaupp]

Thank you. I have given it a go, but unfortunately, it either gives me an "Error 3061: Too Few Parameters. Expected 2" or Access crashes entirely; I have tried to understand why this happens, and apparently (according to other forum posts) this is because I use temporary variables (syntax [TempVars]![SomeVariable]) in the underlying query, which JET somehow interprets as a parameter for the query. Unfortunately I make heavy use of temporary variables throughout my database so it'd be hard to work without.

No luck then... maybe I'll just forget about that idea!

 

[recyan]

Just check out if below gives some guidelines :

A make table query say :
qryTop3FundersRanking

SELECT 
	tblProjects.testProj, 
	tblProjects.Funder, 
	tblProjects.Amount, 
	(SELECT
		Count(*)
	FROM
		tblProjects AS Self
	WHERE
		Self.testProj = tblProjects.testProj
		AND
		Self.Amount >= tblProjects.Amount) AS Rank 
INTO tblqryTop3FundersRanking
	FROM 
		tblProjects
	WHERE 
		((((SELECT
				Count(*)
			FROM
				tblProjects AS Self
			WHERE
				Self.testProj = tblProjects.testProj
				AND
			Self.Amount >= tblProjects.Amount))<=3))
ORDER BY tblProjects.testProj, tblProjects.Amount;

which when run generates a table say :
tblqryTop3FundersRanking

Then run a cross tab query on above table say :
qryTop3FundersCrossTab

TRANSFORM First(tblqryTop3FundersRanking.Funder) AS FirstOfFunder
SELECT 
	tblqryTop3FundersRanking.testProj
FROM 
	tblqryTop3FundersRanking
GROUP BY 
	tblqryTop3FundersRanking.testProj
PIVOT 
	tblqryTop3FundersRanking.Rank;

If is in line with what you need, pls test thoroughly.
Also pls test for behaviour with cases where there are 2 or More Funders with the same amount falling within the Top 3.

Thanks

 

[rkaupp]

Hi, very sorry for the late reply. It works despite adding some complexity. Thank you!