[AccessD] Counts of value ranges

jwcolby jwcolby at colbyconsulting.com
Thu Apr 24 11:17:02 CDT 2008


LOL, in fact there is an error in the way I implemented the formula.  I 
use a GTE and LT kind of thing and your formula used a GTE and LTE.  So 
my totals of the counts returned by your formula came up slightly more 
than the total records being counted.  I knew what to check, fixed the 
formula and am rerunning now.



Drew Wutka wrote:
> Did you have any doubt? ;)
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, April 24, 2008 10:44 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Counts of value ranges
> 
> Drew,
> 
> You appear to be da man.  I built a little table to hold the ranges, 
> then used your SQL statement with the appropriate table and field names 
> pasted in and voila, I have counts.  I have to do a little checking of 
> those counts to see if they are correct but they "look right".
> 
> Drew Wutka wrote:
>> Ooops, you would probably want the ranges to show:
>>
>> SELECT MinValue, MaxValue, (SELECT Count(*) FROM tblDataTable WHERE
>> Value >=T1.MinValue AND Value <=T1.MaxValue) FROM tblRanges AS T1
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Thursday, April 24, 2008 8:44 AM
>> To: Access Developers discussion and problem solving; Dba-Sqlserver
>> Subject: [AccessD] Counts of value ranges
>>
>> Guys,
>>
>> Is there a way in SQL to get counts of records in value ranges:
>>
>> $2,000,000+
>> $1 - $1.99M
>> $750K - $999K
>> $500K - $749K
>> $400K - $499K
>> $300K - $399K
>> $200K - $299K
>> $100K - $199K
>> <$100K
> 

-- 
John W. Colby
www.ColbyConsulting.com



More information about the AccessD mailing list