Finding and Converting negative numbers

  • Thread starter Thread starter steenman2
  • Start date Start date
S

steenman2

Small problem...
I want to make all of the results of a query positive. Keep in mind
that not all of the results are negative, just a about a third of the
400.

Here is what I am talking about...

Results
..68
1.56
-.70
..23
-.34
-1.02
(Each result is affiliated with an ID number)

So I need to filter out the negative and make them positive. Should I
do this in a sub query of the one that created these results? If so
how?

Thanks for the help...
 
Just add a new column to your query and display the absolute value.

I think the formula is...

NewField: Abs([YourFieldName])
 
Take a look at the Abs function

Abs(Results) will strip off the negative signs.
 
Thanks that worked. Now...

I am looking to subtract number inside the same field. and then return
the difference to a new field (column). This data is used to generate a

graph and the this is currently done by hand.

Below is an example of what I am looking for.

Value(s) Result(s)
-70 0
-70 1.02
-68.98 1.09
-67.89 3.11
-71 .71
-69.29 1.52
-67.77


So what I am doing is subtracting row two from row one. The difference
is then put in another field(column) in row one. The process repeats,
row three from two, four from three, five from four, etc, etc. This
occurs for hundreds of rows.

I posted about this earlier and got a possible solution but it only
works half the time.

here is is...
.Value-(SELECT Top 1
.ValueFROM
AS [Value] WHERE
.ID = [Value].ID-1)) AS Range_Result2

Any Help would be much appreciated.
 
How are the rows ordered?
Rows of data have no innate order, so you have to have something that places
them in a specific (and for your particular situation) unique order.

What tells you that -70 comes before -70 which comes before -68.98 etc.?

In other words if these were marbles, and I gave you the bag of marbles with
the row data printed on them, how would you put them in the order you
wanted?
 
Each row has an ID number that is in accending order.


ID Value Result
1 -70 0
2 -70 1.02
3 -68.98 1.09
4 -67.89 3.11
5 -7 .71
6 -69.29 1.52
7 -67.77
 
IF there are no gaps in the ID field then this is simple.

If there are gaps, then this becomes a more complex and also slower.

First Case:

SELECT T.Value,
, T.Value - T1.Value as Result
FROM YourTable as T LEFT JOIN YourTable as T1
ON T.ID = (T1.ID -1 )


Second Case
SELECT T.Value
, T.Value - (SELECT T2.Value
FROM YourTable as T2
WHERE T2.ID = (Select Min(T1.ID)
FROM YourTable as T1
WHERE T1.ID > T.ID)) as Result
FROM YourTable as T
 
Hey thanks for the help.

Could you explain to me what exactly T1 and T2 are. I think I have the
idea but not sure.

Are they just the same as T or am I creating a temporary table to do
the work?

~ steenman2
 
They are separate named instances of the table. I refer to them as aliases
which I've been told is technically incorrect.
 
I am having trouble for some reason with the join operation. Could you
explain the FROM line a little more.

Is Your Table the same thing as T and T1. And with the join I am
getting a syntax error in the join operation. I have tried a couple
fixes and did some reading in the help but to no avail.

Thanks for your help
 
Back
Top