Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Help in solving queries.......

Reply
Thread Tools

Help in solving queries.......

 
 
Patrick
Guest
Posts: n/a
 
      01-26-2005
Hi Group

I want to divide one column by another column and stored that value into
third column.

Example :
Column A, Column B, Column C

I want to divide Column B by Column C and stored that value within Column A

Basically Column A is %
Column B < Column C ( always )

When I use division "/" operator it returns 0 into the Column A

Update Table Set Column A = Column b/Columb C
it gives 0 (zero) in column A
Let me know what I am doing wrong

Column A : float
column b and column c are int
I want to see % in the column A


TIA
Marmik


 
Reply With Quote
 
 
 
 
=?Utf-8?B?Zmluc3RlcjI2?=
Guest
Posts: n/a
 
      01-26-2005
First, to see what the number of decimal places that are allowed in Column A.
My guess is you have it set to zero. Second, you equation results in a
decimal value. Multiple by 100 to get percent.

"Patrick" wrote:

> Hi Group
>
> I want to divide one column by another column and stored that value into
> third column.
>
> Example :
> Column A, Column B, Column C
>
> I want to divide Column B by Column C and stored that value within Column A
>
> Basically Column A is %
> Column B < Column C ( always )
>
> When I use division "/" operator it returns 0 into the Column A
>
> Update Table Set Column A = Column b/Columb C
> it gives 0 (zero) in column A
> Let me know what I am doing wrong
>
> Column A : float
> column b and column c are int
> I want to see % in the column A
>
>
> TIA
> Marmik
>
>
>

 
Reply With Quote
 
 
 
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      01-26-2005
You have to convert A and C to float before doing the division, otherwise
only the result of an integer division is converted to float before the
storage in A:

A = Convert (float, B) / Convert (float, C)

Don't forget to multiply by 100 if you want a % value.

S. L.

"Patrick" <(E-Mail Removed)> wrote in message
news:OPx5zY$(E-Mail Removed)...
> Hi Group
>
> I want to divide one column by another column and stored that value into
> third column.
>
> Example :
> Column A, Column B, Column C
>
> I want to divide Column B by Column C and stored that value within Column
> A
>
> Basically Column A is %
> Column B < Column C ( always )
>
> When I use division "/" operator it returns 0 into the Column A
>
> Update Table Set Column A = Column b/Columb C
> it gives 0 (zero) in column A
> Let me know what I am doing wrong
>
> Column A : float
> column b and column c are int
> I want to see % in the column A
>
>
> TIA
> Marmik
>



 
Reply With Quote
 
John Vinson
Guest
Posts: n/a
 
      01-26-2005
On Wed, 26 Jan 2005 16:27:10 -0600, "Patrick" <(E-Mail Removed)>
wrote:

>Hi Group
>
>I want to divide one column by another column and stored that value into
>third column.


Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

>Example :
>Column A, Column B, Column C
>
>I want to divide Column B by Column C and stored that value within Column A
>
>Basically Column A is %
>Column B < Column C ( always )
>
>When I use division "/" operator it returns 0 into the Column A
>
>Update Table Set Column A = Column b/Columb C
>it gives 0 (zero) in column A
>Let me know what I am doing wrong
>
>Column A : float
>column b and column c are int


That's the problem. An int divided by an int gives you an int.

Try using a Query to dynamically calculate A:

A: Csng([b]) / Csng([c])

If you insist you can use the same expression in your update query,
but don't complain if you end up with INVALID DATA in your database!

John W. Vinson[MVP]
 
Reply With Quote
 
Steve Kass
Guest
Posts: n/a
 
      01-27-2005
John,

To give Patrick the benefit of the doubt, note that he
mentioned needing the quotient "for historical purpose .. later."
It might be completely reasonable to update (i.e., set) the quotient
column A in an insert trigger, so that column A stores the original
ratio of B to C.

From what Patrick has said, I'd assume the value of column A
represents "initial B/C ratio," not "current B/C ratio." The latter,
which he may or may not need, can be calculated on the fly as
you note.

Another reason to store a computed value is when data is
archived, if there is an advantage to indexing the computed
column. Indexed views meet some of these kinds of
needs, but they are more restrictive and less simple. And
the data integrity an indexed view provides can be insured
with a table constraint of CHECK (A = 1.00*B/C), though
I'd only recommend this if the quotient were stored as a
decimal type, not a float.

Steve Kass
Drew University


John Vinson wrote:

>On Wed, 26 Jan 2005 16:27:10 -0600, "Patrick" <(E-Mail Removed)>
>wrote:
>
>
>
>>Hi Group
>>
>>I want to divide one column by another column and stored that value into
>>third column.
>>
>>

>
>Storing derived data such as this in your table accomplishes
>three things: it wastes disk space; it wastes time (almost
>any calculation will be MUCH faster than a disk fetch); and
>most importantly, it risks data corruption. If one of the
>underlying fields is subsequently edited, you will have data
>in your table WHICH IS WRONG, and no automatic way to detect
>that fact.
>
>Just redo the calculation whenever you need it, either as a
>calculated field in a Query or just as you're now doing it -
>in the control source of a Form or a Report textbox.
>
>
>
>>Example :
>>Column A, Column B, Column C
>>
>>I want to divide Column B by Column C and stored that value within Column A
>>
>>Basically Column A is %
>>Column B < Column C ( always )
>>
>>When I use division "/" operator it returns 0 into the Column A
>>
>>Update Table Set Column A = Column b/Columb C
>>it gives 0 (zero) in column A
>>Let me know what I am doing wrong
>>
>>Column A : float
>>column b and column c are int
>>
>>

>
>That's the problem. An int divided by an int gives you an int.
>
>Try using a Query to dynamically calculate A:
>
>A: Csng([b]) / Csng([c])
>
>If you insist you can use the same expression in your update query,
>but don't complain if you end up with INVALID DATA in your database!
>
> John W. Vinson[MVP]
>
>

 
Reply With Quote
 
John Vinson
Guest
Posts: n/a
 
      01-27-2005
On Wed, 26 Jan 2005 21:11:30 -0500, Steve Kass <(E-Mail Removed)> wrote:

>John,
>
> To give Patrick the benefit of the doubt, note that he
>mentioned needing the quotient "for historical purpose .. later."
>...


Good points, all, Steve. Thanks for the reality check!

I see quite a few folks who assume that data must be stored in a table
in order to see it or print it or do anything with it, and this is
usually not true (or not a good enough reason to store it) - but
you're right, there are cases where you do need to store it.

Just FWIW, while Decimal would be ideal for SQL, it's not implemented
very well in the current release of Access. Currency is the closest.

John W. Vinson[MVP]
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Solving simple mathematical equation solving speed Lionel Java 14 06-23-2008 08:01 AM
HELP needed in solving a run time error in ASP.NET hudhu_handhu Software 0 09-13-2006 08:34 AM
Help Me in solving the queries...SUNNY Its_Me_SunnY ASP .Net 0 05-24-2005 12:10 PM
help required for solving the error for file uploading in asp.net msnews.microsoft.com ASP .Net 1 02-15-2005 01:17 PM
Help needed in solving C-errors Dominic Grosleau C Programming 1 07-25-2003 09:48 AM



Advertisments