Prev: Query Join on Multiple Field Indexes
Next: Really weird query responses.
From: Brett on 3 Jul 2008 21:42
I have a query similar to this:

SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
FROM ThisYear, LastYear;

This works fine if they are both positive. However, I have an instance
where the sales for this year is negative and last year is positive:

ThisYear: -80,000
LastYear: 20,000

Result in Access: -100,000 when it should be -60,000.

So how can I get it to subtract a positive number from a negative
number?
From: Duane Hookom on 3 Jul 2008 21:56
What kind of business rule would result in -60,000 rather than the correct
-100,000?

--
Duane Hookom
Microsoft Access MVP


"Brett" wrote:

> I have a query similar to this:
>
> SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
> FROM ThisYear, LastYear;
>
> This works fine if they are both positive. However, I have an instance
> where the sales for this year is negative and last year is positive:
>
> ThisYear: -80,000
> LastYear: 20,000
>
> Result in Access: -100,000 when it should be -60,000.
>
> So how can I get it to subtract a positive number from a negative
> number?
>
From: Brett on 3 Jul 2008 22:04
That was just an example. You could have the first month of the year
with a credit on account and comparing that with the first month of
last year, you have this scenario. So instead of analyzing this in
terms of business, can you answer my question?

Essentially, what I am looking for is a Variance function of some sort
that will show the difference between two numbers, does it exist?
From: Brett on 3 Jul 2008 22:17
Never mind...I'm using the Abs() function and it worked.
From: scubadiver on 4 Jul 2008 05:11

Not wishing to incur your wrath further I don't understand why you have
negative sales ???

"Brett" wrote:

> I have a query similar to this:
>
> SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
> FROM ThisYear, LastYear;
>
> This works fine if they are both positive. However, I have an instance
> where the sales for this year is negative and last year is positive:
>
> ThisYear: -80,000
> LastYear: 20,000
>
> Result in Access: -100,000 when it should be -60,000.
>
> So how can I get it to subtract a positive number from a negative
> number?
>
 | 
Pages: 1
Prev: Query Join on Multiple Field Indexes
Next: Really weird query responses.