Query help needed - need crosstab-like result but I don't have num

[Lori]

I have a table called All_Profile_Risk_Combinations. The fields are risk,
profile, department, and exists (Yes/No field). This table lists every risk
and profile combination and where the profile has the risk. The department
field indicates who owns the risk.

An example row in the table:

View Client (risk) Account (profile) Accounting (department) Yes (exists)

I need to make a query where the output lists the each risks once down the
vertically and lists the profiles across the horizontally once and then
updates the combination with the Yes or No data from the Exists field.

I tried a cross-tab query which started promising but I don't have any
numeric data to tally. I just want the Yes/No data populated for each risk
profile combination.

Is this possible?

 

[Lord Kelvan]

you do have a numeric field the yes/no is numeric

TRANSFORM Count(All_Profile_Risk_Combinations.[exists]) AS
CountOfexists
SELECT All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile
FROM All_Profile_Risk_Combinations
GROUP BY All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile
PIVOT All_Profile_Risk_Combinations.department;


if i understand what you want that should do it

it puts departments accross the top and risks and profiles along the
side and the yes no is the calculation

hope this helps

regards
kelvan

 

[KARL DEWEY]

Try this --
TRANSFORM IIf([exists]=-1,"Yes","No") AS Expr1
SELECT All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile
FROM All_Profile_Risk_Combinations
GROUP BY All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile, IIf([exists]=-1,"Yes","No")
PIVOT All_Profile_Risk_Combinations.department;

Or this --
TRANSFORM IIf([exists]=-1,"Yes","") AS Expr1
SELECT All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile
FROM All_Profile_Risk_Combinations
GROUP BY All_Profile_Risk_Combinations.risk,
All_Profile_Risk_Combinations.profile, IIf([exists]=-1,"Yes","")
PIVOT All_Profile_Risk_Combinations.department;