If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate difference in rows in a query
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 |
#2
|
|||
|
|||
Calculate difference in rows in a query
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; -- Build a little, test a little. "Glenna" wrote: 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 |
#3
|
|||
|
|||
Calculate difference in rows in a query
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 Glenna wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201003/1 |
Thread Tools | |
Display Modes | |
|
|