How to reorganise a report

[keers]

Hi,

I have report based on a query, that pulls out the CompanyName &
FinanceMethod. I want to produce a report that counts the number each
FundingMethod transactions attributed to each companyName. The
difficulty i am having is that i want the FundingMethod to be on the
horizontal axis of the report and the Companyname on the Vertical;
something like;

CompanyName CH FL LP CP Total

Comp A 2 3 0 5 10
Comp B 0 7 3 6 16


I think i am almost there by using setting the Countrol Source to
"=IIf([fundingMethodID]="1",Count([FundingMethodID]))"

(With the "1" changing for each control)

However, each 'count' for the FundingMethID appears on a seperate
line, so the output becomes 'staggered' (I have grouped by
CompanyName)

Any help/direction would be appreciated

Kiers

 

[KARL DEWEY]

Use a crosstab query. Try this --
TRANSFORM Count(FundingMethod) AS CountOfFundingMethod
SELECT CompanyName
FROM YourTableName
GROUP BY CompanyName
PIVOT FundingMethod;

 

[KARL DEWEY]

UNTESTED -- I think you were trying to do this ---

SELECT CompanyName, Sum(IIf([FundingMethod] =1,1,0)) AS [CH],
Sum(IIf([FundingMethod] =2,1,0)) AS [FL], Sum(IIf([FundingMethod] =3,1,0)) AS
[LP], Sum(IIf([FundingMethod] =4,1,0)) AS [CP]
FROM [YourTableName]
GROUP BY CompanyName;