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