Count a char filed and then show Total Urgent

  • Thread starter Thread starter mahmad
  • Start date Start date
M

mahmad

Hi,

I have the following fields:

rep orderack
Blue Y

I would like to be able to count all of the orderack everytime rep = blue
and then total the number of Y in orderack

so firstly i want to count then sum. can this be done in the same query.
based on this query i am then qoing to output the data to data access page.

Thanks for your help

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

You mean you want to count all the "Blue" reps and sum the "Yes"
orderacks. Do you want to sum the orderacks when the rep doesn't equal
"Blue"? Two different things.

The Yes/No fields in access have 3 possible values: NULL, 0 (No), -1
(Yes). You can use ABS(SUM(orderacks)) to get the summation of all the
Yes rows. If you want to just sum the Yes orderacks for only Blue reps
you'll have to do a conditional statement.

SELECT SUM(IIf(rep='Blue',1,0)) As BlueCount,
ABS(SUM(orderacks)) As Total_Yes_OrderAcks
FROM table_name

If you want the results in one query I'd use conditional statements for
both conditions. If you wanted only the count of "Blue," Yes OrderAcks:

SELECT SUM(IIf(rep='Blue',1,0)) As BlueCount,
ABS(SUM(IIf(rep='Blue' AND orderacks=True,1,0)))
As Total_Yes_OrderAcks

FROM table_name
WHERE < criteria >


--
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/AwUBR86U4oechKqOuFEgEQIBYQCeMbfwYT60smwPzIvriFN1ut2UDBwAoJ5v
KDGFE18gFL1h3E1SkhJGBo2K
=9T6i
-----END PGP SIGNATURE-----
 
Hi,

Thanks for your reply, Not sure if what youve suggested will do what i want.

At presnet i have a field called orderacks, these contains 'Y' against every
rep i want all of the Y's totaled for individual rep (red, blue, green,
yellow)

First i want to count all the Y's in the column then sum the total count.

so the column will consist of 100 Y individual Y's and i want them summed up
so i can use the sum amount in another query.

thanks
 
Back
Top