> Is there a way to include the three balance ranges
> in a more complex statement?
hi mike
sure
you did not say which database, so i get to choose the syntax
i choose microsoft access for this one
select clientID
, sum(Iif(Balance<200,1,0)) as under200
, sum(Iif(Balance>200,Iif(Balance<500,1,0),0)) as 200to500
, sum(Iif(Balance>500,Iif(Balance<1000,1,0),0)) as 500to1000
, sum(Iif(Balance>1000,1,0)) as over1000
from clientbalances
group by clientID
this gives a count of the number of balances within each range for each
individual client
if you have a different database, use CASE inside the SUMs
yes, you have to modify the query if you add another range
if you want to count total number of clients in each range, you'd do that
with a series of (sub)queries like yours, all UNIONed together -- yes, you
have to add another subquery if you add another range
any more complex than a few ranges, and you might want to set up a range
table with low and high balance values that the client table can join to
let me know if you want more info
rudy
http://rudy.ca/
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "rudy" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
