CODEWITHSUNDEEP
sqlms-access
dzilla
dzilla

Reputation: 832

How to consolidate crosstab query into one row? Access 2007

I have a cross tab query with several attributes of interest. In this query the presence of a particular attribute is yes or no (1=yes 0=no). Right now, if a person has two of the attributes then they are represented by two rows in the data set. And if they have three of the problems, then they are represented by three rows. I need a way to make multiple attributes show up on the same line. For instance if they have diabetes and tobacco one row would have a "1" under that column instead of right now where there's a 1 followed by all zeroes for diabetes. And the 'tobacco' row has a 0 in every column except for tobacco (where it's a one).

table (Click to enlarge)

is a screen cap of what the output looks like. And I need only one row per patient, regardless of how many problems they have.

below is my sql.

TRANSFORM IIf([tblComorbidity.comorbidityexplanation]=[tblComorbidity.comorbidityexplanation],1,0) AS Morbidity
SELECT
  Person.PersonID,
  Person.Age,
  tblKentuckyCounties.Metro,
  tblKentuckyCounties.Appalachian,
  Person.asiaAdmit,
  Person.Sex
FROM tblKentuckyCounties
  INNER JOIN (tblComorbidity
    INNER JOIN (Person
      INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID
    ) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK
  ) ON tblKentuckyCounties.ID = Person.County
WHERE (((tblComorbidity.comorbidityexplanation)="anxiety and depression"
     Or (tblComorbidity.comorbidityexplanation)="heart"
     Or (tblComorbidity.comorbidityexplanation)="respiratory"
     Or (tblComorbidity.comorbidityexplanation)="uti"
     Or (tblComorbidity.comorbidityexplanation)="diabetes"
     Or (tblComorbidity.comorbidityexplanation)="hypertension"
     Or (tblComorbidity.comorbidityexplanation)="tobacco"))
GROUP BY
  Person.PersonID,
  Person.Age,
  tblKentuckyCounties.Metro,
  tblKentuckyCounties.Appalachian,
  Person.asiaAdmit,
  Person.Sex,
  tblKentuckyCounties.Appalachian,
  tblKentuckyCounties.Metro,
  tblComorbidity.comorbidityexplanation
PIVOT tblComorbidity.comorbidityexplanation;

Upvotes: 0

Views: 1207

Answers (1)

Fink
Fink

Reputation: 3436

Changed the value to the count of the items in case someone has more than 1 condition twice and tried to clean up some formatting. In my limited testing it worked but it might need some tweaks as I don't have your data, but it should get you started.

TRANSFORM nz(Count([tblComorbidity.comorbidityexplanation]),0) AS Morbidity

SELECT Person.PersonID, Person.Age, tblKentuckyCounties.Metro, tblKentuckyCounties.Appalachian, Person.asiaAdmit, Person.Sex

FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County

WHERE tblComorbidity.comorbidityexplanation IN ('anxiety and depression', 'heart', 'respiratory', 'uti', 'diabetes', 'hypertension', 'tobacco')

GROUP BY Person.PersonID, Person.Age, tblKentuckyCounties.Metro, tblKentuckyCounties.Appalachian, Person.asiaAdmit, Person.Sex

PIVOT tblComorbidity.comorbidityexplanation;

Upvotes: 1

Related Questions