comparing rows in a query

[Guest]

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?

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

 

[Guest]

thanks allen, thats a good article but i m not sure how i will use it in my
case

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



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


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

can this be done with inner joins? if so how? any sample code will help

 

[Allen Browne]

Your example suggests:
- the data is to be grouped by region,
- the records will always be in pairs.

If so, you could use a Totals query to get the result you want.

In query design view, depress the Total button on the toolbar (upper Sigma
icon.) Access adds a Total row to the grid.

Under Region, choose Group By.
In the Total row under [sku#], choose Min
Add the [sku#] field again, and choose Max.
Save the query.

Create another query that uses this query as a source table, plus your
original table.
Join MinOfSku# from the query to the sku# in the table.
Add another copy of your original table to the query.
Join MaxOfSku# from the query to the sku# in the 2nd table.
You can now add the other fields to the query, and output the fields you
want.