Calculate difference in rows in a query

[Glenna]

I have a query that calculates how much revenue is claimed by month per job
but then I have to take the new month less the prior month. The problem is
the data is in row format. I don't know how to subtract February from
January, March from but don't know how. I know how to do it in Excel but not
Access. Please help...

Example:
Order Month JTD Clm Variance
101026521 January $511,525 $0
101026521 February $511,525 $0
101029438 January $1,238 $0
101029438 February $3,713 $2,475
101033168 January $21,465 $0
101033168 February $51,460 $29,995
101034011 January $47,524 $0
101034011 February $48,407 $883

 

[KARL DEWEY]

Access will need to distinguish which record to use when subtracting so
instead of month you need a DateTime field.
Then try this query --
SELECT Order, OrderDate, [JTD Clm], (SELECT YourTable.[JTD Clm] - [XX].[JTD
Clm] FROM YourTable AS [XX] WHERE YourTable.Order = [XX].Order AND
Format(YourTable.OrderDate, "yyyymm") = Format(DateAdd("m", -1,
[XX].OrderDate),"yyyymm")) AS Variance
FROM YourTable
ORDER BY Order, OrderDate;