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;

 

[KenSheridan via AccessMonster.com]

As you don't include the year in a column this suggests that the query only
returns data for one calendar year. Assuming this to be the case you'll then
have to force the month values to return a true date/time value of the first
of each month to compare them. Try this, which is based on your current
query, called YourQuery in this example:

SELECT [Order], [Month], [JTD Clm], NZ([JTD Clm] -
(SELECT [JTD Clm]
FROM [YourQuery] AS Q2
WHERE Q2.[Order] = Q1.[Order]
AND CDATE("1 " & [Month]) =
(SELECT MAX(CDATE("1 " & [Month]))
FROM [YourQuery] AS Q3
WHERE Q3.[Order] = Q2.[Order]
AND CDATE("1 " & Q3.[Month]) <
CDATE("1 " & Q2.[Month]))),0)
AS Variance
FROM [YourQuery] As Q1
ORDER BY [Order], CDATE("1 " & [Month]);

Ken Sheridan
Stafford, England