Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-access > #10298
| From | "Phil" <phil@stantonfamily.co.uk> |
|---|---|
| Newsgroups | comp.databases.ms-access |
| Subject | Re: Marking Range of a Number in Select Statement |
| Date | 2015-01-23 18:06 +0000 |
| Organization | Aioe.org NNTP Server |
| Message-ID | <m9u2ji$dg2$1@speranza.aioe.org> (permalink) |
| References | <d3cd64a6-c434-42c8-ad9b-02b9e5911172@googlegroups.com> <fd1efeb7-c0e3-4ec3-a226-af01198e3bac@googlegroups.com> <m9qshf$kqs$1@dont-email.me> <96b5d0cb-3f1e-46f3-9b49-ec3dacbce28b@googlegroups.com> |
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
Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll 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