MS Access-如何根据它们的ID和日期在同一行上显示来自单独记录的两个字段? [英] MS Access - How do I display two fields from separate records on the same line based on their ID and Date?

问题描述

我不知道这是否有可能，或者我的数据库设计不佳，我可以做一个更好的方法吗?

I don't know if this is even possible or perhaps my database is designed poorly and I could have done it a better way?

无论如何，我的数据库都有两个表，分别是"General Data"和"Assessment Data"，它们的设置是为了使患者可以在常规数据"中输入一些基本详细信息，然后每次进行评估时，都会在评估数据"表中输入与该评估相关的数据

Anyway here goes, I have database with two tables as follows "General Data" and "Assessment Data" - they're set up so that a patient can have a few basic details entered into "General Data" and then each time they have an assessment they have data relevant to that assessment entered into the "Assessment Data" table

对不起，狡猾的格式！没有足够的声誉点来上传图像:(

Sorry about the dodgy formatting! Not enough reputation points to upload images :(

常规数据

General 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

评估数据

Assessment Data

**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

我想做的是创建一个查询，该查询将创建类似以下内容的

What i would like to do is create a query that will create something like the following

查询?

Query?

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

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

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

我在另一个论坛上发现了并置函数和查询，但它不适合我想做的事情

I've had a bit of a play around with a concatenating function and query that i found on another forum but it wasn't suitable for what i wanted to do

任何帮助甚至指向正确的方向都是很棒的！

Any help or even pointing me in the right direction would be great!

推荐答案

您将需要使用一些交叉表查询来实现.

You will need to use a couple of crosstab queries to acheive this.

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

First you need to sequence the "Assessment Data" table by using the following query and putting the results into a temporary table called "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];

然后，您可以在两个子查询中使用此临时表，一个用于获取权重值，另一个用于获取高度.

Then you can use this temporary table in two sub queries, one to get the weight values and another to get the height.

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];

然后您可以将这两个子查询结合在一起，然后获得所需的查询

You can then join these two sub queries together to then get your required query

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;

这篇关于MS Access-如何根据它们的ID和日期在同一行上显示来自单独记录的两个字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！