I'm trying to compare referred clients of different racial/ethnic backgrounds based on whether my agency accepts, defers, or denies their cases. Here are the tables and fields involved involved:
TblReferralMaster:
ReferralID (PK)
RaceID* (FK)
DecisionID (FK)
TblRace:
RaceID (PK)
Racecategories
TblDecision:
DecisionID (PK)
decisionname
Here are the queries I've made to allow me to do a crosstab query that reports the numbers of referrals in each category:
Racextab1:
SELECT TblReferralMaster.referralID, TblRace.Racecategories, TblDecision.decisionname
FROM TblRace INNER JOIN (TblReferralMaster INNER JOIN TblDecision ON TblReferralMaster.DecisionID = TblDecision.DecisionID) ON TblRace.RaceID = TblReferralMaster.[RaceID*];
Race-decxtab:
TRANSFORM Count(racextab1.referralID) AS CountOfreferralID
SELECT racextab1.decisionname
FROM racextab1
GROUP BY racextab1.decisionname
ORDER BY racextab1.decisionname, racextab1.Racecategories
PIVOT racextab1.Racecategories;
How to I make a second crosstab where the row headings are decision names, the column headings are race categories, and the values are percentages (number of clients of a given race where a given decision was made / all clients of that race where any decision was made)? I found some other threads on the forum related to crosstab queries and percentages, but either those problems weren't quite the same as mine or my limited SQL isn't enough for me to figure out how to apply those solutions.
Thanks!
TblReferralMaster:
ReferralID (PK)
RaceID* (FK)
DecisionID (FK)
TblRace:
RaceID (PK)
Racecategories
TblDecision:
DecisionID (PK)
decisionname
Here are the queries I've made to allow me to do a crosstab query that reports the numbers of referrals in each category:
Racextab1:
SELECT TblReferralMaster.referralID, TblRace.Racecategories, TblDecision.decisionname
FROM TblRace INNER JOIN (TblReferralMaster INNER JOIN TblDecision ON TblReferralMaster.DecisionID = TblDecision.DecisionID) ON TblRace.RaceID = TblReferralMaster.[RaceID*];
Race-decxtab:
TRANSFORM Count(racextab1.referralID) AS CountOfreferralID
SELECT racextab1.decisionname
FROM racextab1
GROUP BY racextab1.decisionname
ORDER BY racextab1.decisionname, racextab1.Racecategories
PIVOT racextab1.Racecategories;
How to I make a second crosstab where the row headings are decision names, the column headings are race categories, and the values are percentages (number of clients of a given race where a given decision was made / all clients of that race where any decision was made)? I found some other threads on the forum related to crosstab queries and percentages, but either those problems weren't quite the same as mine or my limited SQL isn't enough for me to figure out how to apply those solutions.
Thanks!