Groups > comp.databases.ms-access > #10287

Re: Marking Range of a Number in Select Statement

From	"Ron Paii" <None@none.com>
Newsgroups	comp.databases.ms-access
Subject	Re: Marking Range of a Number in Select Statement
Date	2015-01-22 07:05 -0600
Organization	A noiseless patient Spider
Message-ID	<m9qshf$kqs$1@dont-email.me> (permalink)
References	<d3cd64a6-c434-42c8-ad9b-02b9e5911172@googlegroups.com> <fd1efeb7-c0e3-4ec3-a226-af01198e3bac@googlegroups.com>

Show all headers | View raw


<CDMAPoster@fortunejames.com> wrote in message 
news:fd1efeb7-c0e3-4ec3-a226-af01198e3bac@googlegroups.com...
> On Tuesday, January 20, 2015 at 12:31:25 PM UTC-5, mfl...@scu.edu wrote:
>> Hi,
>> I work at a college and I have several queries that I run on student 
>> data.  Along these lines:
>>
>> Select StudentID, StdntName, CumGPA, etc ....
>>
>> I export the query results to Excel then analyse them in various ways.
>>
>> One thing I do in the Excel file before I use the data is identify the 
>> range the students gpa fell into.  I do this using a lookup table.
>>
>> The lookup table looks like this
>>
>> GPA     Rng
>> 0.99 <2.00
>> 2 2.00 - 2.19
>> 2.2 2.20 - 2.39
>> 2.4 2.40 - 2.59
>> 2.6 2.60 - 2.79
>> 2.8 2.80 - 2.99
>> 3 3.00 - 3.19
>> 3.2 3.20 - 3.39
>> 3.4 3.40 - 3.59
>> 3.6 3.60 - 3.79
>> 3.8 3.80 - 3.99
>> 4 4.00 - 4.19
>> 4.2 >=4.2
>>
>> And in a column in the exported data I run this formula:
>> =vlookup(A6,'GPA Range Lookup'!$A$1:$B$14,2,TRUE)
>>
>> So I end up with:
>> StudentID, StdntName, CumGPA, GPARng
>> 007        Smith,Jo     3.5   3.40 - 3.59
>> 009        Stark,Vera   1.9   <2.00
>>
>>
>> I do this a lot, so I'd like to get the range lookup done in the query.
>>
>> What would be the best way?
>>
>> I could do a lot of embedded IIFs, like this:
>> IIF([gpa]<2,"<2.00",IIF([gpa]<=2.2,"2.00-2.19", IIF( ... )))
>> but that seems hard to maintain as there would be 13 levels.
>>
>> Any ideas appreciated.  There's probably a really obvious simple way I've 
>> not thought of.
>>
>> thanks,
>>
>> Mike
>
> Here's a slightly different way using the Partition function:
>
> tblData
> DID AutoNumber
> StudentID Text
> GPA Double
>
> DID StudentID GPA
> 1   1         3.785411784
> 2   2         2.718281828
> 3   3         4.2
> 4   4         0.8
> 5   5         4.54609188
> 6   6         1.381
> 7   7         3.14159265
> 8   8         4.1999
>
> qryPartitioned:
>
> SELECT StudentID, GPA, CLng(Left(Partition(Int(GPA * 10), 0, 42, 2), 2)) 
> As Range, IIf(Range < 20, '<2.00', IIf(Range > 41, '>=4.2', Format(Range / 
> 10.0, '#0.00') & ' - ' & Format((2 + Range) / 10.0, '#0.00'))) As GPARng 
> FROM tblData;
>
> !qryPartitioned:
>
> StudentID GPA  Range GPARng
> 1 3.785411784  36    3.60 - 3.80
> 2 2.718281828  26    2.60 - 2.80
> 3 4.2          42    >=4.2
> 4 0.8          8     <2.00
> 5 4.54609188   43    >=4.2
> 6 1.381        12    <2.00
> 7 3.14159265   30    3.00 - 3.20
> 8 4.1999       40    4.00 - 4.20
>
> James A. Fortune
> CDMAPoster@FortuneJames.com

That partition function is slick. I would modify your query by subtraction 
0.01 from the upper range to get the OP output.

SELECT StudentID, GPA, CLng(Left(Partition(Int(GPA * 10), 0, 42, 2), 2)) As 
Range, IIf(Range < 20, '<2.00', IIf(Range > 41, '>=4.2', Format(Range / 
10.0, '#0.00') & ' - ' & Format((2 + Range) / 10.0 - 0.01, '#0.00'))) As 
GPARng FROM tblData;

Thread

Marking Range of a Number in Select Statement mflynn@scu.edu - 2015-01-20 09:31 -0800
  Re: Marking Range of a Number in Select Statement Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-01-20 11:16 -0800
    Re: Marking Range of a Number in Select Statement mflynn@scu.edu - 2015-01-20 11:44 -0800
      Re: Marking Range of a Number in Select Statement "Phil" <phil@stantonfamily.co.uk> - 2015-01-21 09:44 +0000
    Re: Marking Range of a Number in Select Statement "Ron Paii" <None@none.com> - 2015-01-21 07:36 -0600
      Re: Marking Range of a Number in Select Statement "Ron Paii" <None@none.com> - 2015-01-21 09:27 -0600
  Re: Marking Range of a Number in Select Statement CDMAPoster@fortunejames.com - 2015-01-21 14:48 -0800
    Re: Marking Range of a Number in Select Statement "Ron Paii" <None@none.com> - 2015-01-22 07:05 -0600
      Re: Marking Range of a Number in Select Statement CDMAPoster@fortunejames.com - 2015-01-22 07:55 -0800
        Re: Marking Range of a Number in Select Statement "Phil" <phil@stantonfamily.co.uk> - 2015-01-23 18:06 +0000
          Re: Marking Range of a Number in Select Statement CDMAPoster@fortunejames.com - 2015-01-29 12:56 -0800
            Re: Marking Range of a Number in Select Statement "Phil" <phil@stantonfamily.co.uk> - 2015-01-29 22:37 +0000
              Re: Marking Range of a Number in Select Statement "Ron Paii" <None@none.com> - 2015-01-30 06:42 -0600
                Re: Marking Range of a Number in Select Statement "Phil" <phil@stantonfamily.co.uk> - 2015-01-30 18:18 +0000
              Re: Marking Range of a Number in Select Statement CDMAPoster@fortunejames.com - 2015-01-30 08:20 -0800
                Re: Marking Range of a Number in Select Statement CDMAPoster@fortunejames.com - 2015-02-05 09:10 -0800
  Re: Marking Range of a Number in Select Statement mflynn@scu.edu - 2015-01-23 08:51 -0800
  Re: Marking Range of a Number in Select Statement mflynn@scu.edu - 2015-01-28 11:05 -0800

csiph-web