is it possible to compare values in 2 different rows? for example if the query returns this:

1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00

i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?

Create a query adding a column incrementing the index of the first table by one.

Create another query joining the table with the query, linking the table index to the 1st query incremented index; perform the (subtraction) calculation in this second query.

interesting, hmmm...i will try to implement that.

llkhoutx, here is what i am trying to do, if my query right returns
this data:


sku# | Region | Cost | EffMonth
124 A $5 7/06
345 A $6 6/06
221 B $4 7/06
345 B $6 4/06
i want it to display this:

sku# | Region | CurrMonth | PrevMonth | Cost-Diff | Prev Month
124 A $5 $6 $1(6-5) 6/06
221 B $4 $6 $2(6-4) 4/06


does ur algorithm apply if i dont have sequential unique key?

If you don't have sequential keys create them in both query, one after the other, with the second being incremental; then join the wo in a third and do your calculation.
Alternatively, use VBA code to cycle though the recordset of the first table, then search for the record in the second, if found perform the calculation, if not, generate an appriate message.

llkhoutx, can u plz tell me what commands i have to use in SQL to add sequential keys?

Use the QBE window to build your query adding another column (the index) which returns a function result incrrementing a counter. Of course, the function has to be initialized before the query is called so that the result starts as 1.

Then look at the SQL if that's really what you're interested in.