Calculate Difference in Rows

[Glenna]

How do I calculate the difference between two numbers in different rows?

 

[arlene ramirez]

"Glenna" wrote:subed going to jail

 

[Arvin Meyer [MVP]]

How do I calculate the difference between two numbers in different rows?

In a report, this may help:

http://support.microsoft.com/kb/290136

 

[Glenna]

The article only tells you how to create a running total. I need the second
row to subtract itself from the previous row total. Does that make sense?

 

[Glenna]

I couldn't find my original post so thank you for including the link. I've
only used this a few times. I really appreciate all the help. I will try
out the info from my original question. Thanks and have a nice day!

 

[Glenna]

I tried it and now it says the following error message:

"You have written a subquery that can return more than one field without
using the EXISTS reserved in the main queries FROM clause. Revise the SELECT
statement of the subquery to request only one field"

I'm sure it's something I did. I created a new query with the table that
contains only the columns order, month and JTD Rev Clm. I brought down those
three columns and then used the next available column to type the following:

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

 

[Glenna]

Step one worked fine. Step 2 is fine until I run it then it says I have
encountered an error and closes down my whole database. I'm sure it's
something I'm doing incorrectly. I need to calculate the difference between
months by row because the users enter the dollars on a form by month. I then
need to show them teh difference from the February to January total revenue
so they know what to claim for the month. I just need February minus January
to show. Not sure if I should do it in a query or on the form itself I'm
sorry but the whole "select formula" thing is way over my head so I don't
know how to trouble shoot why it keeps closing out on me.

Order Month JTD Rev Clm Monthly Claim
101034011 January 47,523.90 47,523.90
101034011 February 48,407.25 883.34
101034011 March 49,607.85 1,200.60

What you appear to have done is enter the whole query as a subquery of
another query.

1. You need to start with a query which returns the columns [Order], [Month]
and [JTD Clm], with one row per Order/Month, but without the variance
between months, which you seemed to say that you'd been able to do. Save
that query.

2. Then you create a new query based on that query, which is what I posted.
To create this open the query designer, but don't add any table to it. Then
switch to SQL view and paste in the following in place of whatever is there:

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]);

3. Then change each occurrence of YourQuery in it to the real name of the
query you saved in 1 above. If any of the column names returned by that
query differ from those in the SQL statement above change those too.

Hopefully you should now be able to open this second query.

Ken Sheridan
Stafford, England

I tried it and now it says the following error message:

"You have written a subquery that can return more than one field without
using the EXISTS reserved in the main queries FROM clause. Revise the SELECT
statement of the subquery to request only one field"

I'm sure it's something I did. I created a new query with the table that
contains only the columns order, month and JTD Rev Clm. I brought down those
three columns and then used the next available column to type the following:

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

Is this the same question as that which you posted yesterday:

[quoted text clipped - 16 lines]

http://support.microsoft.com/kb/290136

 

[De Jager]

How do I calculate the difference between two numbers in different rows?