ms-access - MS Access - 如何根据 ID 和日期在同一行中显示来自不同记录的两个字段？

Question

我不知道这是否可行，或者我的数据库设计得不好，我本可以用更好的方法来完成？

不管怎样，我有两个表的数据库，如下所示“General Data”和“Assessment Data”——它们的设置是为了让患者可以将一些基本细节输入到“一般数据”中，然后每次他们进行评估时，他们都会将与该评估相关的数据输入到“评估数据”表中

抱歉格式不正确!没有足够的信誉点来上传图片:(

一般数据

**ID --     Age (Years) --  Gender --   Town    ------ Referral Source --   Referral Doctor's Name**

1----   12----------------  Male--------    Batlow----- GP Referral----------   Smith

2----   13----------------  Male    --------Coolamon-   GP Referral ---------Bobby

评估数据

**ID -- Date of Assessment -----    Height (cm) ----- Weight (kg)**

1----   6/12/2012--------------------   100-------------------- 80

1----   13/12/2012------------------    100-------------------- 85

2----   25/12/2012  ------------------200--------------------   90

2----   28/12/2012------------------    200 ------------------- 90

我想做的是创建一个查询，它将创建如下内容

查询？

**ID - - Height 1 - - Weight 1 - - Height 2 -- Weight 2 - - Height 3 - - Weight 3 etc...**

1 - - 100  - - - - - - - 80 - - - - - - - 100 - - - - - 85 - - - - - -  - - - - - - - - - - - - - - - - - 

2 - - 200 - - - - - - - 90 - - - - - - - 200 - - - -  - 90 - - - - - - - - - - - - - - - - - - - - - - -

我尝试过在另一个论坛上找到的连接函数和查询，但它不适合我想做的事情

任何帮助甚至为我指明正确的方向都会很棒!

Answer 1

您将需要使用几个交叉表查询来实现此目的。

首先，您需要使用以下查询对“评估数据”表进行排序，并将结果放入名为“Temp_AssessmentData”的临时表中。

    SELECT AssData.Id, AssData.[Date of Assessment], 
        (SELECT Count(*) 
             FROM [Assessment Data] 
             WHERE [Date of Assessment] < [AssData].[Date of Assessment] 
                 AND Id = [AssData].Id
        )+1 AS Sequence 
    INTO Temp_AssessmentData FROM [Assessment Data] AS AssData GROUP BY AssData.Id,
       AssData.[Date of Assessment];

然后您可以在两个子查询中使用这个临时表，一个获取体重值，另一个获取高度。

qAssessmentData_Weight

 TRANSFORM First([Assessment Data].[Weight (cm)]) AS [FirstOfWeight (cm)]
   SELECT Temp.Id
   FROM [Assessment Data] 
       INNER JOIN Temp_AssessmentData AS Temp ON (
          [Assessment Data].[Date of Assessment] = Temp.[Date of Assessment]) 
          AND ([Assessment Data].Id = Temp.Id)
   GROUP BY Temp.Id
   PIVOT "Weight " & [Sequence];

qAssessmentData_Height

 TRANSFORM First([Assessment Data].[Height (kg)]) AS [FirstOfHeight (kg)]
   SELECT Temp.Id
   FROM [Assessment Data] 
       INNER JOIN Temp_AssessmentData AS Temp ON (
          [Assessment Data].[Date of Assessment] = Temp.[Date of Assessment]) 
          AND ([Assessment Data].Id = Temp.Id)
   GROUP BY Temp.Id
   PIVOT "Height" & [Sequence];

然后您可以将这两个子查询连接在一起，然后得到您需要的查询

SELECT Sub_Height.Id, Sub_Weight.[Weight 1], Sub_Height.[Height 1], 
    Sub_Weight.[Weight 2], Sub_Height.[Height 2]
FROM qAssessmentData_Height AS Sub_Height, qAssessmentData_Weight AS Sub_Weight;