Show only last duplicates in access query

问题:

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

回答1:

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

回答2:

Try something like this:

SELECT m.MapNum AS MapNum,
  m.MapName AS MapName,
  mr.CheckOut AS CheckOut,
  mr.CheckIn AS CheckIn
FROM maps m
INNER JOIN (
  SELECT t1.*
  FROM MapsRecords t1
  LEFT JOIN MapsRecords t2 ON t1.MapNum = t2.MapNum
    AND t1.checkOut < t2.checkOut
  WHERE t2.checkOut IS NULL
    AND t1.CheckIn IS NOT NULL
  ) mr ON m.MapNum = mr.MapNum;

This should give you what you want. The inner query gets the record with the latest checkout date, that his checkin is not null. The rest is just a join with maps.

sqlfiddle demo in sql-server

note: this demo is in sql-server, but i see no reason it wouldn't work in access.