ACCESS/SQL: Calculating the difference between rows (not dates)

Question

I looked through the questions here but didn't find one that suited my case.

I'm trying to write a query that will output the difference between rows

Here is a table:

ITEM     CYCLES
--------------------
itemA     5
itemA     17
itemA     20  
itemA     22
itemB     26
itemB     30
itemB     37

it is actually obtained by a query, and with an order by (item, cycles)

here is what I'd like the query to give me:

itemA 12
itemA 3
itemA 2
itemB 4
itemB 7

I have absolutely no idea how to proceed in SQL. Is it even possible ? or do I have to write a function ?

*****************************EDIT*********************************

I appologize for the lack of precision, and even some absurd mistakes. I was rushing out and wrote hastily :/
I'm analyzing item failures, and need to output cycles between failures.

ITEM column is just the item ID, and
CYCLES is the number of cycles the item had when the failure occurred.

And actually looking at it today I don't understand why I put that middle column (A,B,C...) which I don't have in my table.
And indeed, I don't need to output zero values, but there shouldn't be any anyway. I'll try the provided solutions and will get back; thanks for the answers !

Is the pattern correct in the second code block? I would expect to see 12, 3, 2, 4, 6, 7... — RQDQ, Feb 17 '11 at 16:23
fixed it. sorry for the mistakes and lack of correct info :/ — Christian M, Feb 18 '11 at 09:41

score 4 · Accepted Answer · answered Feb 18 '11 at 13:54

4

Here's an updated solution, based on the changes to your question. Note that you'll need to change Qry to the name of your query:

SELECT Qry.Item, Qry.Cycles - (SELECT TOP 1 Cycles FROM Qry AS Q 
                               WHERE Qry.Item=Q.Item
                                 AND Q.Cycles < Qry.Cycles 
                               ORDER BY Q.Cycles DESC) AS Diff
FROM Qry
WHERE (SELECT TOP 1 Cycles FROM Qry AS Q 
       WHERE Qry.Item=Q.Item AND Q.Cycles < Qry.Cycles) Is Not Null
ORDER BY Qry.Item, Qry.Cycles;

This produces the following output:

Item    Diff
itemA   12
itemA    3
itemA    2
itemB    4
itemB    7

I assume that the 6 in your sample output was a typo, since 30 - 26 = 4.

answered Feb 18 '11 at 13:54

mwolfe02

23,787
9
91
161

"I assume that the 6 in your sample output was a typo" /me hides – Christian M Feb 18 '11 at 14:08
What i needed, fitted in just one query. thanks a lot. Now I'll try wrapping my head around it so I can try learning something from that and actually write these kind of queries by myself next time.... – Christian M Feb 18 '11 at 14:27
1

Subqueries have a bit of a learning curve (especially for Access users since they cannot be done using drag and drop in the query design window). However, you can do some really powerful things with them once you understand how they work. See http://allenbrowne.com/subquery-01.html for a good primer on the subject. – mwolfe02 Feb 18 '11 at 14:43
Thanks for the link. I never use the qdw - I'm sure it's supposed to make things simple, but I find code easier to understand / work with. – Christian M Feb 21 '11 at 10:10

score 1 · Answer 2 · answered Feb 17 '11 at 17:43

1

Assuming our columns are named ItemName, Letter, Num, something like the following might do it:

SELECT T1.ItemName, T1.Letter
    , T1.Num, [T2].[Num]-Nz([T1].[Num],[T2].[Num]) AS Expr1
FROM Table1 AS T1 
    LEFT JOIN Table1 AS T2 
        ON (T1.ItemName = T2.ItemName 
            And Asc([T1].[Letter]) = Asc([T2].[Letter]) - 1 )
Where  [T2].[Num] <> Nz([T1].[Num],[T2].[Num])

Note that you cannot create this using the QBE grid. You would need to create in code or in the SQL View.

answered Feb 17 '11 at 17:43

Thomas

63,911
12
95
141

Sorry, if my question is dumb, but... What do you mean by the WHERE condition? Just can't see why it is there. – Andriy M Feb 17 '11 at 18:12
@Andriy M - It excludes values where the difference will be zero. – Thomas Feb 17 '11 at 18:19
All right, thanks. I guessed just as much and it's good to have it confirmed. But then, why? There doesn't seem to be a particular requirement on that head. – Andriy M Feb 17 '11 at 18:24
@Andriy M - I guessed that was a requirement based on the sample output. If that isn't a requirement, then it is not clear in the OP why differences of zero are not showing. – Thomas Feb 17 '11 at 18:31
Agree, it says nothing about zero differences. The example just doesn't include such rows, and it is not clear whether they are guaranteed to be absent nor whether they should be allowed to show up in the result set. – Andriy M Feb 17 '11 at 20:38

score 1 · Answer 3 · answered Feb 17 '11 at 19:50

1

How about:

SELECT b.Item, b.[No], (
   SELECT Top 1 a.No 
   FROM items a 
   WHERE a.No > b.No  
   ORDER BY a.Item,a.No) AS NextNo, 
[NextNo]-[No] AS Result
FROM items AS b;

answered Feb 17 '11 at 19:50

Fionnuala

90,370
7
114
152

That's almost perfect for what I need. I tweaked it a little bit: I changed `WHERE a.No > b.No` to `WHERE a.No > b.No and a.item=b.item`: this way, we only get the difference of cycles for items of the same kind (otherwise we get the difference the first itemB and the last itemA). Then I just have to query the query with a `WHERE result <> null` to filter out the blanks between items, and voila. – Christian M Feb 18 '11 at 13:43

mwolfe02 · Answer 4 · 2011-02-17T16:41:38.863

0

This is much simpler if you have a fourth column with an ID:

Col0 Col1  Col2 Col3
1   itemA   A    5
2   itemA   B   17
3   itemA   C   20
4   itemA   D   22
5   itemB   A   26
6   itemB   B   30
7   itemB   C   37

Then you can use the following query:

SELECT Tbl.Col1, Tbl.Col3 - Prev.Col3 AS Diff
FROM Tbl INNER JOIN Tbl AS Prev 
  ON Tbl.Col0 - 1 = Prev.Col0 
 AND Tbl.Col1 = Prev.Col1

To produce exactly what you requested:

Col1    Diff
itemA   12
itemA    3
itemA    2
itemB    4
itemB    7

You would need to manage the Col0 data to make sure you don't end up with gaps (ie, Autonumber field would be a bad idea).

All of the above having been said, if you let us know what you are really trying to accomplish I'm thinking there is probably a better way to do what you want.

edited Feb 17 '11 at 16:41

answered Feb 17 '11 at 16:35

mwolfe02

23,787
9
91
161

It is possible to do **exactly** what you want without the extra column at the beginning, but it would involve multiple "ranking" subqueries which would be very inefficient for your purposes here. – mwolfe02 Feb 17 '11 at 16:40
@mwolfe02 - how do you do a "ranking" query in Access? – RQDQ Feb 17 '11 at 16:57
See here: http://support.microsoft.com/kb/208946. You would basically be dynamically creating the first column at run time. The ranking queries themselves tend to be slow and you would need to run the query *twice*. Once for `Tbl` and again for its alias `Prev`. If you will have more than 100 records in this table at any time, you would be wise to avoid that approach. It will **really** drag down your query's performance. – mwolfe02 Feb 17 '11 at 17:03
but, wouldn't it also return the difference between the first itemB and the last itemA ? – Christian M Feb 18 '11 at 10:07
No. The second part of the join clause `Tbl.Col1 = Prev.Col1` prevents it from doing comparisons between itemB and itemA (because they are not equal). I assumed from your sample output that this is what you wanted. – mwolfe02 Feb 18 '11 at 11:55

ACCESS/SQL: Calculating the difference between rows (not dates)

4 Answers4

Linked