问题描述
我有一个访问数据库,我记录我出租的地图。所以我有一个名为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
这篇关于只显示访问查询中的最后一个重复项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!