只显示访问查询中的最后一个重复项 [英] Show only last duplicates in access query

问题描述

我有一个访问数据库，我记录我出租的地图。所以我有一个名为MapsOut的表，其中包含了MapNum（Map ID），MapName（Map的名称），CheckOut（地图被借出的日期），CheckIn（返回地图的日期）。

  MapNum MapName CheckOut CheckIn 
 1伦敦01/02/13 07/05/13 
 1伦敦08/05/13 16/06/13 
 1伦敦19/07/13 
 2船体30/01/13 05/03/13 
 2船体06/04/13 01/05/13 
 3德比11/01/13 17/02/13 
 3德比05/09/13 06/10/13 
 4霍夫01/02/13 01/03/13 
  

我想写一个查询，只给我每个MapNum的最后一个记录，但只显示那些被检查回来，所以我知道哪个是最后一个，按照最近最久以前的顺序，在CheckOut列中。所以结果应该如下所示：

  MapNum MapName CheckOut CheckIn 
 4 Hove 01/02/13 01/03 / 13 
 2 Hull 06/04/13 01/05/13 
 3 Derby 05/09/13 06/10/13 
  

我已经做了一个查询，但是我无法使选择DISTINCT工作，因为它仍然显示重复。

这是什么不工作：

  SELECT DISTINCT Maps.MapNum AS MapNum，Maps.MapName，Max（MapsOut1。 CheckOut）AS CheckOut，MapRecords.CheckIn 
 FROM（MapRecords INNER JOIN Maps ON MapRecords.MapNum = Maps.MapNum）INNER JOIN（MapsOut INNER JOIN MapsOut1 ON MapsOut.ID = MapsOut1.ID）ON Maps.MapNum = MapsOut。 MapNum 
 GROUP BY Maps.MapNum，Maps.MapName，MapRecords.CheckIn 
 HAVING（（（MapRecords.CheckIn）不为空））
 ORDER BY Maps.MapNum; 
  

任何帮助将不胜感激。

提前感谢

解决方案

从查询开始，查找每个[MapNum]

$ b $的最新条目b

  SELECT MapNum，Max（CheckOut）AS MaxOfCheckOut 
 FROM MapData 
 GROUP BY MapNum 
  

返回

  MapNum MaxOfCheckOut 
 ------ ------------- 
 1 2013-07-19 
 2 2013-04 -06 
 3 2013-09-05 
 4 2013-02-01 
  

我们可以使用它作为子查询来返回每个行的其余字段，但只有当[CheckIn]不为空时，才能返回其余的字段

  SELECT md.MapNum，md.MapName，md.CheckOut，md.CheckIn 
 FROM 
 MapData md 
 INNER JOIN 
（
 SELECT MapNum ，Max（CheckOut）AS MaxOfCheckOut 
 FROM MapData 
 GROUP BY MapNum 
）AS mx 
 ON md.MapNum = mx.MapNum 
 AND md.CheckOut = mx。 MaxOfCheckOut 
 WHERE md.CheckIn IS NOT NULL 
 ORDER BY md.CheckOut DESC 
  

返回

  MapNum MapName CheckOut CheckIn 
 ------ --- ---- ---------- ---------- 
 3德比2013-09-05 2013-10-06 
 2船体2013-04- 06 2013-05-01 
 4 Hove 2013-02-01 2013-03-01 
  

I have a database in access where I log maps which I loan out. So I have a table called MapsOut, with fields, MapNum(The map ID), MapName (Name of the Map), CheckOut (Date the Maps were loaned out), CheckIn (Date the Maps were returned).

MapNum MapName CheckOut CheckIn
1      London  01/02/13 07/05/13
1      London  08/05/13 16/06/13
1      London  19/07/13 
2      Hull    30/01/13 05/03/13
2      Hull    06/04/13 01/05/13
3      Derby   11/01/13 17/02/13
3      Derby   05/09/13 06/10/13
4      Hove    01/02/13 01/03/13

I want to write a query that gives me only the last record of each MapNum, but only to show those that are checked back in, so I know which was out last, and in the order of most recent to longest time ago, in the CheckOut column. So the results should look like this:

MapNum MapName CheckOut CheckIn
4      Hove    01/02/13 01/03/13
2      Hull    06/04/13 01/05/13
3      Derby   05/09/13 06/10/13

I have made a query, but I can't get the Select DISTINCT to work, as it still shows duplicates.

This is what isn't working:

SELECT DISTINCT Maps.MapNum AS MapNum, Maps.MapName, Max(MapsOut1.CheckOut) AS CheckOut, MapRecords.CheckIn
FROM (MapRecords INNER JOIN Maps ON MapRecords.MapNum = Maps.MapNum) INNER JOIN (MapsOut INNER JOIN MapsOut1 ON MapsOut.ID = MapsOut1.ID) ON Maps.MapNum = MapsOut.MapNum
GROUP BY Maps.MapNum, Maps.MapName, MapRecords.CheckIn
HAVING (((MapRecords.CheckIn) Is Not Null))
ORDER BY Maps.MapNum;

Any help will be greatly appreciated.

Thanks in advance

解决方案

Starting with a query to find the latest entry for each [MapNum]

SELECT MapNum, Max(CheckOut) AS MaxOfCheckOut
FROM MapData
GROUP BY MapNum

returning

MapNum  MaxOfCheckOut
------  -------------
     1  2013-07-19   
     2  2013-04-06   
     3  2013-09-05   
     4  2013-02-01   

we can use that as a subquery to return the rest of the fields for each of those rows, but only if [CheckIn] is not Null

SELECT md.MapNum, md.MapName, md.CheckOut, md.CheckIn
FROM
    MapData md
    INNER JOIN
    (
        SELECT MapNum, Max(CheckOut) AS MaxOfCheckOut
        FROM MapData
        GROUP BY MapNum
    ) AS mx
        ON md.MapNum = mx.MapNum 
            AND md.CheckOut = mx.MaxOfCheckOut
WHERE md.CheckIn IS NOT NULL
ORDER BY md.CheckOut DESC

returning

MapNum  MapName  CheckOut    CheckIn   
------  -------  ----------  ----------
     3  Derby    2013-09-05  2013-10-06
     2  Hull     2013-04-06  2013-05-01
     4  Hove     2013-02-01  2013-03-01

这篇关于只显示访问查询中的最后一个重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！