Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10275 > unrolled thread

Marking Range of a Number in Select Statement

Back to article view | Back to comp.databases.ms-access

Contents

  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

#10275 — Marking Range of a Number in Select Statement

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

[toc] | [next] | [standalone]

#10277

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

I created Table2 with 1 field; MyNum.  I made 12 recs, 1 to 12.

I created Table1 with 3 fields; Low, High, BetweenStr.  I created 3 recs; low/high = 1-4, 4, 5-8,8, 9-12,12.

I then created this function
Public Function BetweenLookup(lngNum As Long)
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
    rst.FindFirst lngNum & " Between Low And High"
    BetweenLookup = rst!BetweenStr  'the GPA range
    rst.Close
    Set rst = Nothing
End Function

My query was
SELECT Table2.MyNum, BetweenLookup([MyNum]) AS BetweenStr
FROM Table2;

Produced expected results.  The second column calls the function.

[toc] | [prev] | [next] | [standalone]

#10278

On Tuesday, January 20, 2015 at 11:16:47 AM UTC-8, Patrick Finucane wrote:
> 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
> 
> I created Table2 with 1 field; MyNum.  I made 12 recs, 1 to 12.
> 
> I created Table1 with 3 fields; Low, High, BetweenStr.  I created 3 recs; low/high = 1-4, 4, 5-8,8, 9-12,12.
> 
> I then created this function
> Public Function BetweenLookup(lngNum As Long)
>     Dim rst As Recordset
>     Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
>     rst.FindFirst lngNum & " Between Low And High"
>     BetweenLookup = rst!BetweenStr  'the GPA range
>     rst.Close
>     Set rst = Nothing
> End Function
> 
> My query was
> SELECT Table2.MyNum, BetweenLookup([MyNum]) AS BetweenStr
> FROM Table2;
> 
> Produced expected results.  The second column calls the function.

Thanks Patrick.  I'll try this and let you know how I get on.

[toc] | [prev] | [next] | [standalone]

#10279

:
> 
> Thanks Patrick.  I'll try this and let you know how I get on.
> 

As an alternative tou might try

Function GPA(NoIn As Single)

    If NoIn < 2 Then
        GPA = 0.99
        Exit Function
    ElseIf NoIn >= 4.2 Then
        GPA = 4.2
        Exit Function
    End If
    
    GPA = Int(NoIn * 5) / 5
       
End Function

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10281

"Patrick Finucane" <patrickfinucanetx@gmail.com> wrote in message 
news:fea8b7ce-15c6-4725-8f2a-de632b1f634f@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
>
> I created Table2 with 1 field; MyNum.  I made 12 recs, 1 to 12.
>
> I created Table1 with 3 fields; Low, High, BetweenStr.  I created 3 recs; 
> low/high = 1-4, 4, 5-8,8, 9-12,12.
>
> I then created this function
> Public Function BetweenLookup(lngNum As Long)
>    Dim rst As Recordset
>    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
>    rst.FindFirst lngNum & " Between Low And High"
>    BetweenLookup = rst!BetweenStr  'the GPA range
>    rst.Close
>    Set rst = Nothing
> End Function
>
> My query was
> SELECT Table2.MyNum, BetweenLookup([MyNum]) AS BetweenStr
> FROM Table2;
>
> Produced expected results.  The second column calls the function.
>

Because your range increased by 0.2, you can calculate it in the query and 
eliminate the function

iif([GPA]<2,"<2.0",iif([GPA]>=4.2,">=4.2",format(round([GPA],1),"0.00") & 
" - " & format(round([GPA]+0.2,1)-0.21,"0.00"))) 

[toc] | [prev] | [next] | [standalone]

#10282

"Ron Paii" <None@none.com> wrote in message 
news:m9o9vn$mfl$1@dont-email.me...
>
>
> "Patrick Finucane" <patrickfinucanetx@gmail.com> wrote in message 
> news:fea8b7ce-15c6-4725-8f2a-de632b1f634f@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
>>
>> I created Table2 with 1 field; MyNum.  I made 12 recs, 1 to 12.
>>
>> I created Table1 with 3 fields; Low, High, BetweenStr.  I created 3 recs; 
>> low/high = 1-4, 4, 5-8,8, 9-12,12.
>>
>> I then created this function
>> Public Function BetweenLookup(lngNum As Long)
>>    Dim rst As Recordset
>>    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
>>    rst.FindFirst lngNum & " Between Low And High"
>>    BetweenLookup = rst!BetweenStr  'the GPA range
>>    rst.Close
>>    Set rst = Nothing
>> End Function
>>
>> My query was
>> SELECT Table2.MyNum, BetweenLookup([MyNum]) AS BetweenStr
>> FROM Table2;
>>
>> Produced expected results.  The second column calls the function.
>>
>
> Because your range increased by 0.2, you can calculate it in the query and 
> eliminate the function
>
> iif([GPA]<2,"<2.0",iif([GPA]>=4.2,">=4.2",format(round([GPA],1),"0.00") & 
> " - " & format(round([GPA]+0.2,1)-0.21,"0.00")))

Fixed the above calc, VBA math got in the way, Had to add explicit 
conversion functions.

IIf([GPA]< 2, "<2.0", IIf([GPA]>= 4.2, ">=4.2", Format(Int([GPA]) + 
(Int(([GPA]- CDec(Int([GPA]))) * 10#) \ 2#) * 0.2, "0.00") & " - " & 
Format(Int([GPA]) + (Int(([GPA]- CDec(Int([GPA]))) * 10#) \ 2#) * 0.2 + 
0.19, "0.00")))
 

[toc] | [prev] | [next] | [standalone]

#10286

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

[toc] | [prev] | [next] | [standalone]

#10287

<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;
 

[toc] | [prev] | [next] | [standalone]

#10288

On Thursday, January 22, 2015 at 8:05:24 AM UTC-5, Ron Paii wrote:
> <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;

That's obviously better :-).  My awareness has been on the fritz lately due to my heavy workload.  The Partition function would be good for histogram class interval counting.  It would be an interesting exercise to generalize the Partition function so that the ranges wouldn't have to be uniform, perhaps by using a variant array variable to specify the edges and an integer to specify how many edges.

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#10298

On 22/01/2015 15:55:17,  wrote:
> On Thursday, January 22, 2015 at 8:05:24 AM UTC-5, Ron Paii wrote:
>> <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(Ra
> nge /
>> > 10.0, '#0.00') & ' - ' & Format((2 + Range) / 10.0, '#0.00'))) As GPARn
> g
>> > 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 subtractio
> n
>> 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;
> 
> That's obviously better :-). My awareness has been on the fritz lately due
> to my heavy workload. The Partition function would be good for histogram
> class interval counting. It would be an interesting exercise to generalize
> the Partition function so that the ranges wouldn't have to be uniform, per
> haps by using a variant array variable to specify the edges and an integer
> to specify how many edges.
> 
> James A. Fortune
> CDMAPoster@FortuneJames.com
> 


For amusement messing around with your idea, but having trouble passing
values.

Private Type PartitionLimits
    Rounding As Single
    LowerLimit As Single
    LowerValue As Single
    UpperLimit As Single
    UpperValue As Single
End Type

Function GPA3(NoIn As Single, PL As PartitionLimits)
'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)
…
…
End Function

It compiles OK but when I put the Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2) in
the Debug window I get a compile error before the function starts. Am I being
stupid?

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10313

On Friday, January 23, 2015 at 1:06:49 PM UTC-5, Phil wrote:
 
> For amusement messing around with your idea, but having trouble passing
> values.
> 
> Private Type PartitionLimits
>     Rounding As Single
>     LowerLimit As Single
>     LowerValue As Single
>     UpperLimit As Single
>     UpperValue As Single
> End Type
> 
> Function GPA3(NoIn As Single, PL As PartitionLimits)
> 'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)
> ...
> ...
> End Function
> 
> It compiles OK but when I put the Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2) in
> the Debug window I get a compile error before the function starts. Am I being
> stupid?
> 
> Phil
> 
> ---
> This email has been checked for viruses by Avast antivirus software.
> http://www.avast.com

Any functions called via Access SQL must be declared Public.  Could that be the problem?  It wouldn't hurt to declare the type PartitionLimits as Public also.

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#10314

On 29/01/2015 20:56:32,  wrote:
> Private Type PartitionLimits
>>     Rounding As Single
>>     LowerLimit As Single
>>     LowerValue As Single
>>     UpperLimit As Single
>>     UpperValue As Single
>> End Type
>>
>> Function GPA3(NoIn As Single, PL As PartitionLimits)
>> 'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)

Thanks, James.

Makes no difference. Still get a Compile Error - ByRef argument mismatch.

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10315

"Phil" <phil@stantonfamily.co.uk> wrote in message 
news:maecnb$m50$1@speranza.aioe.org...
> On 29/01/2015 20:56:32,  wrote:
>> Private Type PartitionLimits
>>>     Rounding As Single
>>>     LowerLimit As Single
>>>     LowerValue As Single
>>>     UpperLimit As Single
>>>     UpperValue As Single
>>> End Type
>>>
>>> Function GPA3(NoIn As Single, PL As PartitionLimits)
>>> 'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)
>
> Thanks, James.
>
> Makes no difference. Still get a Compile Error - ByRef argument mismatch.
>
> Phil
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> http://www.avast.com
>

A query cannot pass a "PartitionLimits" type using setting Integer or Long. 
"Single" may also cause you problems, I would us Integer.

Function GPA3(NoIn As Integer, PL As Integer)
 

[toc] | [prev] | [next] | [standalone]

#10317

On 30/01/2015 12:42:54, "Ron Paii" wrote:
> 
> 
> "Phil" <phil@stantonfamily.co.uk> wrote in message
> news:maecnb$m50$1@speranza.aioe.org...
>> On 29/01/2015 20:56:32,  wrote:
>>> Private Type PartitionLimits
>>>>     Rounding As Single
>>>>     LowerLimit As Single
>>>>     LowerValue As Single
>>>>     UpperLimit As Single
>>>>     UpperValue As Single
>>>> End Type
>>>>
>>>> Function GPA3(NoIn As Single, PL As PartitionLimits)
>>>> 'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)
>>
>> Thanks, James.
>>
>> Makes no difference. Still get a Compile Error - ByRef argument mismatch.
>>
>> Phil
>>
>> ---
>> This email has been checked for viruses by Avast antivirus software.
>> http://www.avast.com
>>
> 
> A query cannot pass a "PartitionLimits" type using setting Integer or
> Long. "Single" may also cause you problems, I would us Integer.
> 
> Function GPA3(NoIn As Integer, PL As Integer)
> 
> 
> 

Don't think that will work, Ron as PL is a Type variable with 5 values, not a
single integer.

At this stage, I am only messing about with VBA, in the Debug window, so no
queries are involved

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#10316

On Thursday, January 29, 2015 at 5:37:39 PM UTC-5, Phil wrote:
> On 29/01/2015 20:56:32,  wrote:
> > Private Type PartitionLimits
> >>     Rounding As Single
> >>     LowerLimit As Single
> >>     LowerValue As Single
> >>     UpperLimit As Single
> >>     UpperValue As Single
> >> End Type
> >>
> >> Function GPA3(NoIn As Single, PL As PartitionLimits)
> >> 'Debug.Print GPA3(3, 0.2, 2, 0.99, 4.2, 4.2)
> 
> Thanks, James.
> 
> Makes no difference. Still get a Compile Error - ByRef argument mismatch.
> 
> Phil
> 
> ---
> This email has been checked for viruses by Avast antivirus software.
> http://www.avast.com

I see the problem now, but not the solution yet :-).  I tried the following function:

Public Function MetaPartition(varField As Variant, varEdges As Variant, intNumIntervals As Integer) As Variant
Dim I As Integer

MetaPartition = Null
If IsNull(varField) Then Exit Function
If intNumIntervals < 1 Then Exit Function
If varField < varEdges(0) Then
  MetaPartition = ":" & varEdges(0)
  Exit Function
End If
If varField > varEdges(intNumIntervals - 1) Then
  MetaPartition = varEdges(intNumIntervals - 1) & ":"
  Exit Function
End If
For I = 1 To intNumIntervals - 1
  If varField < varEdges(I) Then
    MetaPartition = varEdges(I - 1) & ":" & varEdges(I)
    Exit Function
  End If
Next I
End Function

and tested it on a form with the following code:

Private Sub cmdTestMetaPartition_Click()
Dim varEdges(100) As Variant
Dim varArr As Variant

varArr = Array(1, 2, 3, 5, 10)
MsgBox (MetaPartition(5.5, varArr, 5))
End Sub

That returned "5:10" as expected.  I set up a table called tblValues with a field called theValue.  The following SQL:

SELECT MetaPartition(theValue,Array(1,2,3,5,10),5) FROM tblValues;

Resulted in: "Data type mismatch in criteria expression."

Then I tried:

SELECT MetaPartition(5.1,Array(1,2,3,5,10),5) FROM tblValues;

I got the same result.

I vaguely remember a post in this NG from David Fenton about how the engine that runs the SQL doesn't have the same functions as the IDE available.  If that is the case, the Array() function would be the function that's not available.  The help for the Array() function mentions VBA.Array().  Using a ParamArray might be a possibility, but doesn't look very clean.  I'll have to think about this one some more.

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#10331

On Friday, January 30, 2015 at 11:20:58 AM UTC-5, cdmap...@fortunejames.com wrote:

>available.  The help for the Array() function mentions VBA.Array().  Using a >ParamArray might be a possibility, but doesn't look very clean.  I'll have to >think about this one some more.
> 
> James A. Fortune
> CDMAPoster@FortuneJames.com

The ParamArray approach wasn't too bad.  I only tested it for the values shown in the example.

Public Function MetaPartition(ParamArray varInput() As Variant) As Variant
Dim I As Integer
'Field Value, Number of Edges, N edges
MetaPartition = Null
'Make sure the number of Edges is an integer
If Not IsNumeric(varInput(1)) Then Exit Function
If varInput(1) <> Int(varInput(1)) Then Exit Function
'Check for wrong number of arguments
If UBound(varInput()) <> varInput(1) + 1 Then Exit Function
If IsNull(varInput(0)) Then Exit Function
If varInput(1) < 1 Then Exit Function
If varInput(0) < varInput(2) Then
  MetaPartition = ":" & varInput(2)
  Exit Function
End If
If varInput(0) > varInput(varInput(1) + 1) Then
  MetaPartition = varInput(varInput(1) + 1) & ":"
  Exit Function
End If
For I = 2 To varInput(1) + 1
  If varInput(0) < varInput(I + 1) Then
    MetaPartition = varInput(I) & ":" & varInput(I + 1)
    Exit Function
  End If
Next I
End Function

qryTestPartition:
SELECT tblValues.theValue, MetaPartition2(theValue,5,1,2,3,5,10) AS Range FROM tblValues;

!qryTestPartition:
theValue Range
0.5 :1
1.1 1:2
5.5 5:10
7.7 5:10
11.1 10:

Perhaps I'll be able to try out what you did to see what's going on with that.

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#10295

On Tuesday, January 20, 2015 at 9:31:25 AM UTC-8, 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

Thanks Ron and James.  These look good.  I have had a million things come up at work and hope to get the time to test this today or Monday.

[toc] | [prev] | [next] | [standalone]

#10310

On Tuesday, January 20, 2015 at 9:31:25 AM UTC-8, 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

Well.  I never cease to be amazed at the genius of the people who respond to questions in this group.  James Fortune's idea to use Partition worked just great.  And now I have another great tool to use now that I know about Partition.
Not only that, but I had never thought of writing a vba function and installing it into the db so that it can be called in a query, another fantastic tool I'll use often in the future.

Thanks guys.

Mike

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web