CODEWITHSUNDEEP
sqlms-access
Nafcio
Nafcio

Reputation: 1

MS Access query difference between current and previous record

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.

Upvotes: 0

Views: 1475

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 0

Tedo G.
Tedo G.

Reputation: 1565

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

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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

Upvotes: 1

Related Questions