SELECT语句、交叉表的额外行？Access 2007

Question

我正在和一些生物统计人员一起工作，他们当然喜欢SAS。我在下面有一个select语句，用于测试一个人可能存在的某些问题。如果一个人有心脏问题和呼吸问题，那么他们的patientID就会被列出两次。如何为每个发病率添加额外的1或0列？因此，如果我有三个问题，它们是“心”、“肺”和“尿路感染”，则会生成一个额外的列，该列的值为1或0，这基于一个人是否存在该问题。

我想我可以使用Excel使其成为交叉表，但最终它将需要采用该格式。下面是我的SELECT语句。谢谢，各位！

编辑：

TRANSFORM First(Person.PersonID) AS Morbidity
SELECT Person.PersonID, Person.Age, Person.Sex
FROM tblKentuckyCounties INNER JOIN ((tblComorbidity INNER JOIN comorbidVisits ON tblComorbidity.ID = comorbidVisits.comorbidFK) 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)="hypertension" Or (tblComorbidity.comorbidityexplanation)="pressure sores" Or (tblComorbidity.comorbidityexplanation)="tobacco" Or (tblComorbidity.comorbidityexplanation)="uti"))
GROUP BY Person.PersonID, Person.Age, Person.Sex, tblComorbidity.comorbidityexplanation
PIVOT Person.Race;

Answer 1

这是未经测试的：

TRANSFORM IIf([c.comorbidityexplanation]=
      [c.comorbidityexplanation],1,0) AS Morbidity
SELECT p.PersonID, p.Age, p.Sex, p.Race
FROM tblKentuckyCounties kc
INNER JOIN ((tblComorbidity c
INNER JOIN comorbidVisits cv
   ON c.ID = cv.comorbidFK) 
INNER JOIN (Person p
INNER JOIN tblComorbidityPerson cp
   ON p.PersonID = cp.personID) 
   ON c.ID = cp.comorbidityFK) 
   ON kc.ID = p.County
GROUP BY p.PersonID, p.Age, p.Sex, p.Race
PIVOT c.comorbidityexplanation