ms-access - 如何从多行中查找帖子的分手日期

Question

我有一个大约 1000 个帖子的结构如下表

SRL     Post_Name   Name                    Start_Date  End_Date    Reason
441a    Lecturer    Dr (Ms)Prem Kali Sharma 01-11-1974  02-03-1989  Retired
441b    Lecturer    Post Vacant             03-03-1989  31-07-1989  Vacant
441c    Lecturer    Dr Aditya Prachandiya   01-08-1989  30-07-1994  Worked1
441d    Lecturer    Dr Aditya Prachandiya   01-08-1994  31-07-1999  Worked2
441e    Lecturer    Dr Aditya Prachandiya   01-08-1999  25-08-2000  Worked1
441f    Lecturer    Dr Aditya Prachandiya   26-08-2000  05-03-2010  Leave
441g    Lecturer    Dr Aditya Prachandiya   06-03-2010  30-06-2016  Retired
441h    Lecturer    Post Vacant             01-07-2016  06-11-2017  Vacant
441i    Lecturer    Dr (Ms) Ranjana Pandey  07-11-2017  06-11-2018  Temporary
442a    Reader      Dr HC Gupta             01-07-1958  30-06-1990  Retired
442b    Reader      Post Vacant             01-07-1990  25-10-1990  Vacant
442c    Reader      Dr Agam Prasad Tyagi    26-10-1990  25-10-1995  Worked5
442d    Reader      Dr Agam Prasad Tyagi    26-10-1995  25-10-2000  Worked1
442e    Reader      Dr Agam Prasad Tyagi    26-10-2000  05-12-2003  Worked2
442f    Reader      Dr Agam Prasad Tyagi    06-12-2003  30-06-2029  Working

基本上我想做的是有一个访问查询来查找从“441a 到 441i”和“442a 到 442f”是否有任何日期间隔来检查日期的输入错误。

标签： ms-access

Answer 1

需要提取数字前缀作为组标识符。我用表达式在表中创建了一个计算字段：Left([SRL],3)。然后是一个嵌套查询来提取下一条记录的 Start_Date：

SELECT *, End_Date + 1 AS NextStart, (SELECT TOP 1 Dupe.Start_Date FROM Table1 AS Dupe WHERE Dupe.SRL_Group = Table1.SRL_Group AND Dupe.SRL > Table1.SRL) AS NextRecStart FROM Table1;

如果 NextStart 和 NextRecStart 不相等，则存在日期间隔。