如何将交叉表查询合并到一行？Access 2007

Question

我有一个具有几个感兴趣的属性的交叉表查询。在此查询中，特定属性的存在是yes或no (1=yes 0=no)。现在，如果一个人有两个属性，那么它们将由数据集中的两行表示。如果它们有三个问题，那么它们由三行表示。我需要一种方法来使多个属性显示在同一行上。例如，如果他们有糖尿病和烟草，那么一行在该列下面会有一个"1“，而不是现在糖尿病的”1“后面跟着所有的0。除了烟草之外，“烟草”行的每一列都有一个0(它是一个1)。

​

(Click to enlarge)

是输出外观的屏幕截图。我只需要每个病人排一排，不管他们有多少问题。

下面是我的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;

Answer 1

将值更改为项目计数，以防有人两次具有超过1个条件，并尝试清理某些格式。在我有限的测试中，它工作了，但它可能需要一些调整，因为我没有你的数据，但它应该会让你开始。

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;