extract part of a value

L

Lori

I've ready all of the postings about separating alpha-numeric, pulling apart
numbers but none of the posts deal with decimals. How would I get a query to
look at in this case # of hours billed each week and pull out only the .5 in
the 40.5, 35.5, 8.5, etc.?

I'm trying to write a query that will count the number of 5s, 7s, 8, 35s and
40s in a weekly total but I also need the .5s.

Any ideas????
 
M

MGFoster

Lori said:
I've ready all of the postings about separating alpha-numeric, pulling apart
numbers but none of the posts deal with decimals. How would I get a query to
look at in this case # of hours billed each week and pull out only the .5 in
the 40.5, 35.5, 8.5, etc.?

I'm trying to write a query that will count the number of 5s, 7s, 8, 35s and
40s in a weekly total but I also need the .5s.

Any ideas????
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what, exactly, you want, so....

To get just the integer part of a decimal number: Int(40.5) = 40

To get just the decimal part of the number: 40.5 - Int(40.5) = 0.5

Not sure if you want to count or sum the values, so....

TO COUNT:

SELECT Int(number_column) As Integers, COUNT(*) As CountOfNumbers
FROM table_name
WHERE <criteria, if any>
GROUP BY number_column

The above qry will produce an output like this:

Integers CountOfNumbers
5 25
7 2
8 10
35 5
40 16

The CountOfNumbers is just made up to show output.

TO SUM:

If you want to sum the Integers just do this in the SELECT clause:

SELECT SUM(Int(number_column)) As SumOfIntegers
FROM ....

The number_column is just a name I picked to represent your number
column name.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSQoyM4echKqOuFEgEQJAJwCdGLfKQE5cPN8IRd2iVn/rv++jriwAoM48
gsiQlpVnFFqUhI+tIxYBOGJT
=18dY
-----END PGP SIGNATURE-----
 
K

KARL DEWEY

To count 0.5's try this --
SELECT Sum(IIF([YourField] - Int([YourField]) = .5, 1, 0)) AS [Count_of_0.5]
FROM YourTable;
 
Top