Comparing “Consecutive” Rows in Ms Access

Question

I have a table that looks like this:

ID   Subid      Time    Value     Value2
1        1       3       100       2
1        1       4       100       2
2        2       3       200       1       
2        2       4       200       2
4        4       4       10        4
5        6       3       10        2       
5        6       4       12        2

What i want to do now is just show the entries where there is a difference from time 3 to time 4. So the result should look like this:

ID   Subid      Time    Value     Value2
2        2       3       200       1       
2        2       4       200       2
4        4       4       10        4
5        6       3       10        2       
5        6       4       12        2

Conditions for an entry being in the second part are as follows: If there are two entries with the same ID and Subid, but a different month then check if value and value2 are equal. If these are BOTH equal, then remove the entry, else show the entry. If no match can be found in the other time then also show the entry. Typically this table would have a lot of entries that match, so I want to make my life easier by sorting those out.

I have to use MS Access (2010) to do this.

Thank you for your help.

EDIT after Remou's answer:

I now have the problem that I do not really have the table shown above, but that I create it with another query. So for now I just put the query to create that table in the four places Remou uses it... I'm sure there is a better way to do this, but I can't figure it out...

SELECT t2.*

FROM (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value FROM

( HUGE SELECT HERE with 3 joins) t

   WHERE t.Time=4)  AS t2 
           LEFT JOIN 
   (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
    FROM

( HUGE SELECT HERE with 3 joins) t

    WHERE t.Time=3)  AS t1 
         ON t2.Id = t1.Id
         WHERE t2.value<>t1.value
         OR t2.value2<>t1.value2 
         OR t1.Id Is Null

UNION ALL

   SELECT t1.* FROM
   (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
    FROM

( HUGE SELECT HERE with 3 joins) t

     WHERE t.Time=3) AS t1
           LEFT JOIN
    (SELECT t.ID, t.SubID, t.Time, t.Value, t.value2, t.value
    FROM

( HUGE SELECT HERE with 3 joins) t

    WHERE t.Time=4) AS t2
          ON t1.Id=t2.Id
          WHERE t1.value<>t2.value
         OR t1.value2<>t2.value2 
          OR t2.Id Is Null

ORDER BY Id, EntryNo;

Answer 1

Since this is MS Access, how about creating a query with two copies of t (Access calls the second t_1) (just connect every field, or look at this http://i49.tinypic.com/rix175.jpg.)

Then right-click, edit SQL, and you should see something like this:

    SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
    FROM t INNER JOIN t AS t_1 ON (t.Value2 = t_1.Value2) 
    AND (t.Value = t_1.Value) 
    AND (t.Subid = t_1.Subid) 
    AND (t.Time = t_1.Time-1) 
    AND (t.ID = t_1.ID);

Which you can re-write like this:

    SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
    FROM t INNER JOIN t AS t_1 ON 
    (t.Time = t_1.Time-1)
    AND (t.ID = t_1.ID) 
    AND (t.Subid = t_1.Subid) 
    AND ((t.Value <> t_1.Value) OR (t.Value2 <> t_1.Value2));

Note the "t.Time = t_1.Time-1" edit, and the AND statement; this will give you the query results from Time = 3. Now do a UNION on this, slightly modified, this time with "t.Time-1 = t_1.Time" instead to get the Time = 4 results. Then add one at the end to catch the unique entries:

    SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
    FROM t INNER JOIN t AS t_1 ON 
    (t.Time = t_1.Time-1) 
    AND (t.ID = t_1.ID) 
    AND (t.Subid = t_1.Subid) 
    AND ((t.Value <> t_1.Value) OR (t.Value2 <> t_1.Value2))

    UNION SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
    FROM t INNER JOIN t AS t_1 ON 
    (t.Time-1 = t_1.Time)  
    AND (t.ID = t_1.ID)
    AND (t.Subid = t_1.Subid) 
    AND ((t.Value <> t_1.Value) OR (t.Value2 <> t_1.Value2))

    UNION SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
    FROM t INNER JOIN t AS t_1 ON 
    (t.Time-1 = t_1.Time) 
    AND (t.ID <> t_1.ID) 
    AND (t.Subid <> t_1.Subid);

Answer 2

How about:

SELECT t2.*
FROM (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4)  AS t2 LEFT JOIN (SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3)  AS t1 ON t2.ID = t1.ID
WHERE t2.Value<>t1.Value 
OR t2.Value2<>t1.Value2 
OR t1.ID Is Null
UNION ALL
SELECT t1.* FROM
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=3) t1
LEFT JOIN
(SELECT t.ID, t.Subid, t.Time, t.Value, t.Value2
FROM Test2 t
WHERE t.Time=4) t2
ON t1.ID=t2.ID
WHERE t1.Value <> t2.Value
OR  t1.Value2 <> t2.Value2
OR t2.ID Is Null
ORDER BY ID