Help in solving queries.......

[Patrick]

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

 

[Guest]

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.

 

[Sylvain Lafontaine]

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.

 

[John Vinson]

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() / 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]

 

[Steve Kass]

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

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() / 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]

 

[John Vinson]

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]