MS Access query difference between current and previous record

Question

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 |           |
------------|-------------|------|---------------|---------------|-----------|

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    |
------------|-------------|------|---------------|---------------|-----------|

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.

score 1 · Answer 1 · answered Sep 12 '16 at 14:29

1

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

answered Sep 12 '16 at 14:29

Darren Bartrup-Cook

18,362
1
23
45

`CDATE()` casts to `DATETIME` which arguably isn't appropriate for a time period. NZ() only works within the Access UI so I would avoid it. `LEFT JOIN` explicitly generates nulls which are definitely to be avoided (noting you are using `NZ()` to ultimately avoid them). Otherwise an up-vote ;) – onedaywhen Sep 12 '16 at 15:08
True, I try and avoid `NZ` if I can as it's not even Jet / ACE SQL but part of VBA (_I think_). Saying that though MySQL has IFNULL and maybe COALESCE(?). I used `LEFT JOIN` to return the NULLS for part_number 1 rather than add them at the end with a UNION (or some other way) - just felt it made the query shorter, although not necessarily faster. I used `CDATE` so the data would come out in the format shown on the OPs final table rather than as a whole number. I do like your use of `current` and `prior` - much better alias names than `T1` and `T2`. – Darren Bartrup-Cook Sep 12 '16 at 15:25

Tedo G. · Answer 2 · 2016-09-12T14:30:48.237

0

First of all, add an unique autoincrement ID field to your table, if you have not one.

Then make a left join of your table to itself:

SELECT
    A.*
    , B.Date1 AS [Date2]
    , DateDiff ("n", A.Date1, B.Date1) AS [Difference]
FROM
    YourTable AS A
        INNER JOIN
    YourTable AS B
        ON A.ID = B.ID - 1

edited Sep 12 '16 at 14:30

answered Sep 12 '16 at 12:25

Tedo G.

1,556
3
17
29

Why the need for an autonumber field? **schedule_nr** and **part_number** uniquely identify each record and the running order. – Darren Bartrup-Cook Sep 12 '16 at 14:04
If the presented data is only data in his table, yes, you are right. But if this table has other records too, it may be not unique. – Tedo G. Sep 12 '16 at 14:06
I'd still go with the presented data as that's what he says the data is. Your DateDiff needs an `n` instead of an `m` for minutes - `m` is for months. – Darren Bartrup-Cook Sep 12 '16 at 14:25
@DarrenBartrup-Cook, Thanks, Ive changed it. – Tedo G. Sep 12 '16 at 14:31

onedaywhen · Answer 3 · 2016-09-12T15:04:30.047

From the data it appears your definition of "prior row" seems to be:

has the same schedule_nr and
has the part_number minus one (big assumption** that there are no gaps in your data)

This can be used the search condition for a JOIN.

We also need to allow for the special case where part_number = 1 i.e. when there is no prior row then simply default to zero.

The two cases can be UNION-ed together:

SELECT current.schedule_nr, current.part_number, 
       DATEDIFF('N', prior.Date2, current.Date1) AS difference_in_minutes
  FROM YourTable AS prior
       INNER JOIN YourTable AS current
           ON prior.schedule_nr = current.schedule_nr
              AND prior.part_number = ( current.part_number - 1 )
UNION
SELECT current.schedule_nr, 1 AS part_number,
       0 AS difference_in_minutes
  FROM YourTable AS current
 WHERE current.part_number = 1

** if there are gaps in the part_number sequence for a schedule_nr then the predicate for "prior row" is slightly more complex i.e. find the maxium part_number less than the current part_number for the same schedule_nr.

Small modification of this query and it looks very good, dates are counted correctly. Thanks. — Nafcio, Sep 13 '16 at 08:52

MS Access query difference between current and previous record

3 Answers3

Linked