Simple division

[Guest]

Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but I can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Sylvain Lafontaine]

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one of
these two values but if you have more complex expressions and parenthesis,
the situation will be more complicated if you are trying to cheat.

 

[Guest]

Sylvain, thank you so much once again for your help. I am unclear as to how
I convert my fields, or exactly what that means. Could you maybe elaborate,
or tell me how to enter it in the grid pane?

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one of
these two values but if you have more complex expressions and parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Sylvain Lafontaine]

Grid pane? I don't know, I never use them. However, my expectation would
be that you the expression as it is normally written in the sql statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Guest]

I am using ADP, and I have very limited SQL knowledge. When I make views I
use the query designer. When I need to make expressions I use the grid pane
to type my code.

Grid pane? I don't know, I never use them. However, my expectation would
be that you the expression as it is normally written in the sql statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Guest]

Sylvain, maybe this will help. This is the SQL. The problem is that the
results for the '%Drops Placed' column are '0' for both records, when they
should clearly not be. Do you have any idea why I would be getting these
results?

SELECT qty_drops, qty_drops_placed, qty_drops_placed / qty_drops AS
[%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

Grid pane? I don't know, I never use them. However, my expectation would
be that you the expression as it is normally written in the sql statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Sylvain Lafontaine]

And what happens if you try to write "Convert(float, qty_drops_placed) /
Convert(float, qty_drops)" in the grid pane in place of only writing
"qty_drops_placed/qty_drops" in the column field?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, maybe this will help. This is the SQL. The problem is that the
results for the '%Drops Placed' column are '0' for both records, when they
should clearly not be. Do you have any idea why I would be getting these
results?

SELECT qty_drops, qty_drops_placed, qty_drops_placed / qty_drops AS
[%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

Grid pane? I don't know, I never use them. However, my expectation
would
be that you the expression as it is normally written in the sql
statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as
to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

:

Your syntax appears to be OK. Are you using this expression in a
Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint
as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced]
from
table1

Of course, in this simple case, all you have to do is to convert only
one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible,
but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops
Spliced]

both fields are tinyint. Thanks for any help.

 

[Robert Morley]

What you would need to do is to change it to the following:

SELECT qty_drops, qty_drops_placed, CONVERT (float, qty_drops_placed) /
CONVERT (float, qty_drops) AS [%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

You may also want to change your WHERE statement to:

WHERE qty_drops <> 0

.... just to prevent a divide-by-zero condition.



Rob

Sylvain, maybe this will help. This is the SQL. The problem is that the
results for the '%Drops Placed' column are '0' for both records, when they
should clearly not be. Do you have any idea why I would be getting these
results?

SELECT qty_drops, qty_drops_placed, qty_drops_placed / qty_drops AS
[%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

Grid pane? I don't know, I never use them. However, my expectation
would
be that you the expression as it is normally written in the sql
statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as
to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

:

Your syntax appears to be OK. Are you using this expression in a
Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint
as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced]
from
table1

Of course, in this simple case, all you have to do is to convert only
one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible,
but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops
Spliced]

both fields are tinyint. Thanks for any help.

 

[Guest]

Sylvain and Rob. Thank you so much, you guys are geniuses. I did notice
that before I tried your solution I got the correct answer only where the
answer was '1'. Anyway, it works great. Now is there a way to change
..3081234 to 31, rouding up with no decimal? I tried entering the expression
in another column with a '=' in front, surrounded by () and followed by
'*100'. I thought this would convert to a percent, but it did not. I am at
a loss as to how to round. Also, do you two know this stuff in your heads, or
do you look it up somewhere? Where can I look it up so as to limit the time
all of you have to spend on simple questions like this? Thank you again.

Sylvain, maybe this will help. This is the SQL. The problem is that the
results for the '%Drops Placed' column are '0' for both records, when they
should clearly not be. Do you have any idea why I would be getting these
results?

SELECT qty_drops, qty_drops_placed, qty_drops_placed / qty_drops AS
[%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

Grid pane? I don't know, I never use them. However, my expectation would
be that you the expression as it is normally written in the sql statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain, thank you so much once again for your help. I am unclear as to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

:

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Guest]

I guess I answered my own question. I found some help in the Help menu
(imagine that), and fooled around with it to get it to work. This is what I
came up with.

ROUND(CONVERT (float, qty_drops_placed) / CONVERT (float, qty_drops), 2) * 100

This produces the required results, but I do not know if it may cause
problems. Do you see any reason why I should not do this? Thanks again.

Sylvain and Rob. Thank you so much, you guys are geniuses. I did notice
that before I tried your solution I got the correct answer only where the
answer was '1'. Anyway, it works great. Now is there a way to change
.3081234 to 31, rouding up with no decimal? I tried entering the expression
in another column with a '=' in front, surrounded by () and followed by
'*100'. I thought this would convert to a percent, but it did not. I am at
a loss as to how to round. Also, do you two know this stuff in your heads, or
do you look it up somewhere? Where can I look it up so as to limit the time
all of you have to spend on simple questions like this? Thank you again.

Sylvain, maybe this will help. This is the SQL. The problem is that the
results for the '%Drops Placed' column are '0' for both records, when they
should clearly not be. Do you have any idea why I would be getting these
results?

SELECT qty_drops, qty_drops_placed, qty_drops_placed / qty_drops AS
[%Drops Placed]
FROM dbo.tblmdu_tracking
WHERE (NOT (qty_drops IS NULL))

Grid pane? I don't know, I never use them. However, my expectation would
be that you the expression as it is normally written in the sql statement.

Are you talking about an ADP project or a MDB file with linked tables?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Sylvain, thank you so much once again for your help. I am unclear as to
how
I convert my fields, or exactly what that means. Could you maybe
elaborate,
or tell me how to enter it in the grid pane?

:

Your syntax appears to be OK. Are you using this expression in a Select
statement or somewhere else?

Also, you will have rounding & truncating problem dividing two tinyint as
the result will also be a tiniyint. You should convert or cast these
values
into float (8 bytes) or real (4 bytes) before making the division:

select convert (float, a) / convert (float, b) as [%Drops Spliced] from
table1

Of course, in this simple case, all you have to do is to convert only one
of
these two values but if you have more complex expressions and
parenthesis,
the situation will be more complicated if you are trying to cheat.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greetings all. Does anyone know the syntax to do a simple divison
calculation in a view? I have tried wording it every way possible, but
I
can
not get it to work. I need to do this expression.

dbo.tblmdu.qty_drops_spliced/dbo.tblmdu.qty_drops AS [%Drops Spliced]

both fields are tinyint. Thanks for any help.

 

[Robert Morley]

ROUND was going to be my suggestion, so you're on the right track.
Off-hand, I don't know of any reason why not to use it. If you're working
within an ADP, you can also simply apply a "Percent" format to the column
without doing any rounding. This may or may not be desirable, depending
what you're doing...for example, if you want to retain all the decimal
places because you plan on transferring the info to Excel, this is a good
way to go; if you don't want anybody to ever know there was ever anything
after the decimal place, then it's not such a good idea.

As to what I know in my head, I have to do *exactly* what you're doing on a
regular basis in my database, so it was pretty much right there in my head
for this issue. I've still only got about 2 years experience with SQL
Server, though, so there's a lot I'm still learning. My main resources are
the help file, the MS knowledge base, google (if I can figure out some good
keywords to search on), and of course, here.



Rob