Access 2003 - 可能的子查询问题 [英] Access 2003 - Possible Subquery Problem

问题描述

我正在尝试在查询中创建一个查询，其中有多个外键，并且在每组外键中，有多个日期。我想检索每个外键的最新日期，然后我想删除剩余的日期。我不想删除重复外键的记录，只删除较旧的日期。我如何比较日期，当我这样做的时候，只剩下最新的日期？最终结果应该是对于每组外键，应该只有一个日期。


即，


FK日期


1 1/1/2003

1 2/21/2004

1 3/14/2005

2 10/19/2003

2 12/1/2003

2 5/6/2007


结果应该是看起来像：


FK日期


1

1

1 3 / 14/2005

2

2

2 5/6/2007



谢谢。


KOelschlaeger

I am trying to create a query within a query in which there are more than one foreign keys, and within each set of foreign keys, there is more than one date. I want to retrieve the latest date for each foreign key and then I want to delete the remaining dates. I don''t want to delete the record of the duplicated foreign key, just the older dates. How do I go about comparing the dates, and when I do, having it where only the latest date is remaining? The end result should be that for each set of foreign keys, there should only be one date.

i.e.,

FK Date

1 1/1/2003
1 2/21/2004
1 3/14/2005
2 10/19/2003
2 12/1/2003
2 5/6/2007

Result should look like:

FK Date

1
1
1 3/14/2005
2
2
2 5/6/2007


Thank you.

KOelschlaeger

推荐答案

试试这个...

Try this ...

展开 < span class =codeDivider> | 选择 | Wrap | 行号

建议的代码似乎正在删除除指定的Date列之外的其他列。它似乎也删除了我想在指定的Date列中保留的日期。这是我正在使用的一个例子（TTID是主键）：[我希望它看起来像是在这个例子之下]。


FdrID NegDate NegFinishDate NegMiles TTID StartDate

3 1/1/2006 3/31/2006 35.12 6960 4/1/2006

3 1/1/2006 3/31/2006 35.12 2430 1/12/2002

3 1/1/2006 3/31/2006 35.12 121 1/9/1999

4 1/1/2006 3/31 / 2006 17.6 6861 3/18/2006

4 1/1/2006 3/31/2006 17.6 2431 2/23/2002

4 1/1/2006 3 / 31/2006 17.6 122 12/12/1998

5 1/1/2006 3/31/2006 18.25 6860 3/4/2006

5 1/1 / 2006年3月31日18.25 2432 3/16/2002

5 1/1/2006 3/31/2006 18.25 123 11/14/1998


FdrID NegDate NegFinishDate NegMiles TTID StartDate

3 1/1/2006 3/31/2006 35.12 6960 4/1/2006

3 2430 1/12/2002

3 121 1/9/1999

4 1/1/2006 3/31/2006 17.6 6861 3/18/2006

4 2431 2/23/2002

4 122 12/12/1998

5 1/1/2006 3/31/2006 18.25 6860 3/4/2006

5 2432 3 / 16/2002

5 123 11/14/1998


谢谢。

The suggested code seems to be deleting other columns than the specified Date column. It also seems to delete the date I would like to keep in the specified Date column. Here is an example of what I''m working with (The TTID is the primary key): [What I would like for it to look like is below this example].

FdrID NegDate NegFinishDate NegMiles TTID StartDate
3 1/1/2006 3/31/2006 35.12 6960 4/1/2006
3 1/1/2006 3/31/2006 35.12 2430 1/12/2002
3 1/1/2006 3/31/2006 35.12 121 1/9/1999
4 1/1/2006 3/31/2006 17.6 6861 3/18/2006
4 1/1/2006 3/31/2006 17.6 2431 2/23/2002
4 1/1/2006 3/31/2006 17.6 122 12/12/1998
5 1/1/2006 3/31/2006 18.25 6860 3/4/2006
5 1/1/2006 3/31/2006 18.25 2432 3/16/2002
5 1/1/2006 3/31/2006 18.25 123 11/14/1998

FdrID NegDate NegFinishDate NegMiles TTID StartDate
3 1/1/2006 3/31/2006 35.12 6960 4/1/2006
3 2430 1/12/2002
3 121 1/9/1999
4 1/1/2006 3/31/2006 17.6 6861 3/18/2006
4 2431 2/23/2002
4 122 12/12/1998
5 1/1/2006 3/31/2006 18.25 6860 3/4/2006
5 2432 3/16/2002
5 123 11/14/1998

Thank you.

试试这个...

Try this ...

展开 | 选择 | Wrap | 行号

这是否为que你正在运行？

Is this the query you are running?

展开 | 选择 | Wrap | 行号

这篇关于Access 2003 - 可能的子查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！