Query calculation

[Charter]

I have created a field in Query that shows the difference in 2 columns:

2011 2012 New
5000 6000 1000

When I put a negative in 2012 it doubles in the Diff column

2011 2012 New
5000 -5000 10000

I want it to show a 0 for the difference.

How do I get it to show a 0

Thank you

[pbaldy]

Well, 10000 is mathematically correct. You can use the Abs() function to force both number to positives.

[Charter]

If the first column is 5000 and the second column is negative 5000 and if I am totally the two columns, I want it to show zero in the third column but it shows 10,000 and that is not right.

I have to take the current year of sales and subtract what was done the previous year to show either an increase or decrease.

It works great as long as there is no negative in the column.

[admessing]

See the post by pbaldy....you need to have both numbers as positive numbers for it to work.

Ex:
Sales 2011 = 5000
Sales 2012 = 5012
Difference = 12

BUT, if you have it this way:
Sales 2011 = 5000
Sales 2012 = -5012
Difference = 10012.....this happens because it is getting the LITERAL difference between the 2 values.

If you want to show loss of sales..its the same thing;
Sales 2011 = 5000
Sales 2012 = 4960
Difference = -40....this is because the program calculates it as (4960 - 5000) = -40

For it to show 0 (zero)...just have equal values in both columns:
Sales 2011 = 5000
Sales 2012 = 5000
Difference = 0

The problem that I think you are encountering in that you do not have a base from which to work from. If you are trying to show actual sales for each year, you may want to keep a running count of ALL years, then choose to compare the ACTUAL sales for each year against one another:

Ex:
Year 1 = 1560
Year 2 = 1600 (Diff = 40) {1600 - 1560}
Year 3 = 1840 (Diff = 240) {1840 - 1600}
Total Sales = 5000 {Year 1 + Year 2 + Year 3}

[pbaldy]

Like I said, it is mathematically correct. The difference between a positive 5000 and a negative 5000 is 10000.

Did you try the Abs() function I recommended??

[scubagal]

I am having a similar problem except that I do not know how to create the field in my query to make a calculation. Ex...

Day 1 Meter Reading is 123456
Day 2 meter reading is 123567
Day 3 Meter Reading is 123678

What I need is a calculation that will subtract Day 1 from Day 2 and display the difference, subtract day 2 from day 3 and display the difference, and so on for the whole month. I am learning as I go so I have no formal training on Access so it is very helpful to have this forum as a resource. Thanks for any help.

[pbaldy]

This should help:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[Charter]

CUSTOMER 2011 SA 2011 CONTROLS 2012 SA 2012 CONTROLS EQUIP CON PY BASE RENEWALS SA 1 26,664.00 28,272.00 1,608.00 1,608.00 SA 2 43,960.56 2,071.44 - SA 3 18,430.00 5,198.00 19,668.96 5,387.04 1,238.96 189.04 1,428.00 SA 41 6,008.00 1,060.00 6,222.00 1,098.00 214.00 38.00 252.00 SA 11 43,675.00 8,945.00 46,294.08 9,481.92 2,619.08 536.92 3,156.00 SA 12 11,218.00 4,582.00 11,640.20 4,639.80 422.20 57.80 480.00 SA 13 - SA 15 34,046.00 5,542.00 36,089.04 5,874.96 2,043.04 332.96 2,376.00 SA 16 32,154.00 11,298.00 (32,154.00) (11,298.00) (64,308.00) (22,596.00) 43,452.00 (86,904.00)SA 14 6,048.00 6,420.00 372.00 372.00 What can I put in the Equip column to show (32,154.00)

Thank you
-

[pbaldy]

That is completely unreadable.