MS Access 查询当前记录和上一条记录的区别 [英] MS Access query difference between current and previous record

问题描述

我在 MS Access 2010 中有表格，其中包含以下示例数据:

I have table in MS Access 2010 with below sample data:

schedule_nr | part_number | name | date1         | date2         | difference|
------------|-------------|------|---------------|---------------|-----------|
12345       |     1       |Part1 |01-02-16 2:50  |01-02-16 2:50  |           |
12345       |     2       |Part2 |01-02-16 2:54  |01-02-16 2:54  |           |
12345       |     3       |Part3 |01-02-16 3:03  |01-02-16 3:03  |           |
45678       |     1       |Part1 |02-02-16 2:15  |02-02-16 2:15  |           |
45678       |     2       |Part2 |02-02-16 2:19  |02-02-16 2:19  |           |
45678       |     3       |Part3 |02-02-16 2:27  |02-02-16 2:27  |           |
23456       |     1       |Part1 |02-02-16 13:17 |02-02-16 13:17 |           |
23456       |     2       |Part2 |02-02-16 13:21 |02-02-16 13:21 |           |
23456       |     3       |Part3 |02-02-16 13:30 |02-02-16 13:30 |           |
------------|-------------|------|---------------|---------------|-----------|

在差异"列中，我需要在 date2 和 date1 与上一行之间存在差异.从每个 part_number = 1 计数差异应该从头开始.所以决赛桌应该如下所示:

In column "difference" I need to have difference between date2 and date1 from previous row. From each part_number = 1 counting difference should be done from beginning. So the final table should look like below:

schedule_nr | part_number | name | date1         | date2         | difference|
------------|-------------|------|---------------|---------------|-----------|
12345       |     1       |Part1 |01-02-16 2:50  |01-02-16 2:50  |0:00:00    |
12345       |     2       |Part2 |01-02-16 2:54  |01-02-16 2:54  |0:04:00    |
12345       |     3       |Part3 |01-02-16 3:03  |01-02-16 3:03  |0:09:00    |
45678       |     1       |Part1 |02-02-16 2:15  |02-02-16 2:15  |0:00:00    |
45678       |     2       |Part2 |02-02-16 2:19  |02-02-16 2:19  |0:04:00    |
45678       |     3       |Part3 |02-02-16 2:27  |02-02-16 2:27  |0:08:00    |
23456       |     1       |Part1 |02-02-16 13:17 |02-02-16 13:17 |0:00:00    |
23456       |     2       |Part2 |02-02-16 13:21 |02-02-16 13:21 |0:04:00    |
23456       |     3       |Part3 |02-02-16 13:30 |02-02-16 13:30 |0:09:00    |
------------|-------------|------|---------------|---------------|-----------|

是否可以在 MS Access 中将其作为一个查询进行?

Is it possible to do it as one query in MS Access ?

感谢大家的帮助和提示.看起来它现在可以工作了.再次感谢.

Thnks everybody for help and hints. It looks like it works now. Thanks again.

推荐答案

此查询将给出您想要的结果 - 加入 schedule_nr 和下一个 part_number.
如果 T2 返回 NULL，则它是系列中的第一个数字，因此请使用 NZ 将其与 T1 的日期交换，后者将返回 0.

This query will give the results you're after - join on the schedule_nr and the next part_number.
If T2 returns a NULL then it's the first number in the series so make use of NZ to swap that with T1's date which will return 0.

SELECT       T1.schedule_nr
            ,T1.part_number
            ,T1.sname
            ,T1.date1
            ,T1.date2
           , CDATE(NZ(T2.date2,T1.date1) - T1.date1) AS Difference
FROM        Table2 T1 LEFT JOIN Table2 T2 ON
                T1.schedule_nr = T2.schedule_nr AND
                T1.part_number = T2.part_number+1

这篇关于MS Access 查询当前记录和上一条记录的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！