如何跨三个表提取最大记录数据 [英] How to extract maximum record data across three tables

问题描述

我有一个记录人们参与散步的数据库。为了满足规范化规则，有三个表 - People，Walks和Walk_participation_history。 Walks表提供了有关步行的额外信息，例如日期和地点，walk_participation_history（WPH）表格提供了每个人参与的详细信息，例如他在步行中的位置，照片编号等。每个人都可以参加一个或者几次散步，所以每次散步时都会有WPH记录。


我需要提取一个人参与最近一次行走的细节他参加过。所以我用所有三个表中的必填字段进行了查询，并尝试使用GroupOn People.Person_ID和Max（Walks.Date）来获取每个人最近的行走。问题是如何将其他字段输入到输出中。一旦我点击查询的Totals按钮，似乎坚持查询中的每个字段都应用了某种聚合，而我想将每个人的最新记录中的步行号，位置，照片编号等携带到输出。


查看互联网上的帖子我得知答案是使用子查询，但由于涉及三个表我不能完全理解如何做到这一点。任何帮助将不胜感激。

人们................. WPH ............. .......步行S

... Person_ID ............. Person_ID ........... ... Wa lk_no

...年龄....................Walk_number .........日期

... Suburb_ID .............位置............... Ve nue

.....等.....................等.................. .... ...等

I have a database recording the participation of people on walks. To meet normalization rules there are three tables - People, Walks and Walk_participation_history. The Walks table gives extra information about the walk such as the date and venue, and the walk_participation_history (WPH) table gives details of each person''s involvement such as his position on the walk, photo number etc. Each person may participate on one or several walks, so there will be a WPH record for each time he goes on a walk.

I need to extract details of a person''s involvement in the most recent walk he has attended. So I have made a query with the required fields from all three tables, and tried to use GroupOn People.Person_ID and Max(Walks.Date) to get each person''s most recent walk. The problem is how to get the other fields into the output. Once I click on the Totals button for the query it seems to insist that every field in the query has some sort of aggregation applied, whereas I want to carry the walk number, position, photo number etc from the most recent record for each person into the output.

Looking at posts on the internet I gather that the answer is to use a subquery, but because three tables are involved I can''t quite get my head around how to do it. Any help would be much appreciated.

PEOPLE.................WPH....................WALK S
...Person_ID.............Person_ID..............Wa lk_no
...Age................... ..Walk_number.........Date
...Suburb_ID.............Position...............Ve nue
.....etc.....................etc.................. .......etc

推荐答案

我怀疑答案是创建一个包含您想要的所有数据的查询，然后将此条件与您的联接表中的日期相对应（Walks .Date）

I suspect the answer is to create a query with all the data you want and then put this criteria against the date in your join table (Walks.Date)

展开 | 选择 | Wrap | <温泉n class =codeLinkonclick =LineNumbers（this）;>行号

谢谢，菲尔。看起来很有希望。但是我没有清楚表达我的需要。我不仅仅想得到给定人的答案，我想创建一份报告，列出所选择的一部分人，其中包括该人最近的行走细节。


我有一个包含所有必填字段的查询，我试图将建议的DMax放入其中的Start_Date列，但它只是给了我整个批次的最新记录，而不是最新的每人。


感谢日期提醒。 （我实际上已经调用了字段Start_date，但为了简单起见，我只是在我的OP中使用了Date，忘记了保留字限制。）

Thank you, Phil. That looks promising. However I failed to express my need clearly. I don''t just want to get the answer for a given person, I want to create a report listing a chosen subset of people showing (amongst other things) the person''s most recent walk details.

I have a query with all the required fields, and I tried to put the suggested DMax into the Start_Date column of that, but it just gave me the latest record of the whole lot, not the latest per person.

Thanks for the reminder on Date, too. (I actually had called the field Start_date, but I just used Date in my OP for simplicity, forgetting about the reserved word restriction.)

它应该可以工作。你确定你有类似Person_ID =的东西吗？ &安培;我建议的标准中的Person_ID。 Person_ID可能需要进一步的限定，例如

It should work. Are you sure you have something like "Person_ID = " & Person_ID in the criteria I suggested. The Person_ID may need further qualification like

展开 | 选择 | Wrap | 行号

这篇关于如何跨三个表提取最大记录数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！