How to count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, Hope someone can help me.

I have a table that has 2 columns: "Name" and "Fee".

I would like to add another column Named "Result" that will show +1 if the
Fee is positive and it will show -1 if the Fee is negative as below

Name Fee Result
AAA 2.00 1
AAA -2.00 -1
BBB 2.00 1
BBB 2.00 1
BBB -2.00 -1

Thanks.
 
Why? That seems redundent.

To do it, simply use and IF statement that says if less than 0 then -1 if
greater than zero, then 1. But I'm not sure why you'd do this. You CAN
count the number of records with positive values and/or the number of
records with negative values without the need to add a column like you
suggest.
 
Please show me how to program an IF Statement.

The table that I am trying to query has over 100,000 names and sometimes the
Data Entry person makes mistake with the Fee $ (Positive$ instead of
Negative$). Therefore I have make sure that if the Sum divided by the count
will give me 2.00. Thanks.
 
Thanks, it works great.

Allen Browne said:
Type this into a fresh column of a *query*, in the Field row:
Result: Sgn([Fee])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bltony said:
Hello, Hope someone can help me.

I have a table that has 2 columns: "Name" and "Fee".

I would like to add another column Named "Result" that will show +1 if the
Fee is positive and it will show -1 if the Fee is negative as below

Name Fee Result
AAA 2.00 1
AAA -2.00 -1
BBB 2.00 1
BBB 2.00 1
BBB -2.00 -1

Thanks.
Click to expand...
Click to expand...
 
Please show me how to program an IF Statement.

The table that I am trying to query has over 100,000 names and sometimes the
Data Entry person makes mistake with the Fee $ (Positive$ instead of
Negative$). Therefore I have make sure that if the Sum divided by the count
will give me 2.00. Thanks.
Click to expand...

As they've said - there is NO good reason to store this derived value
*in your table*. I don't see how having the plus or minus one value
would help with the sum problem though!

You can put a textbox on the Form, or a calculated field in the Query,
with the expression Sgn([Fee]) to get the +1 or -1 value (for whatever
good that will do you).

John W. Vinson[MVP]
 
Back
Top