How to vlookup in Access

[Fred]

I am trying to come up with a way to lookup values in Access as easily as I
can in Excel.

My application is looking up scores. An example lookup table below explains
what I'm after:
TABLE1
Measure (pct) Number score Letter grade Disposition
1% 40 A
Pass
6% 36 B
Pass
10% 32 C
Marginal
Substd
Fail- HOLD

In Excel it is simple. I evaluate a measure say 7%
Excel looks down the table and stops at the 6% line since7% is larger than
6% but smaller than 10%
Excel returns 32 or C or Marginal depending on which place I specify

Example (vlookup(0.07, TABLE1,3) yields a "C" vlookup(0.07, TABLE1,4) yields
"Marginal"

How can I accomplish the same end in Access without getting into
programming?

Thanks for your help
Fred


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Mike Storr]

Create a table that stores this info and you can create any query you want
to
act against it. No code necessary. Example structure might be...

FieldName DataType
---------------------------------
Measure Number (format as percent)
Score Number (Integer or Long)
Grade Text
Disposition Text

Any any other fields you may need.

Mike Storr
www.veraccess.com
"Fred" <fr************ **@wymans.com> wrote in message
news:40******** @corp.newsgroup s.com...

I am trying to come up with a way to lookup values in Access as easily as I can in Excel.

My application is looking up scores. An example lookup table below explains what I'm after:
TABLE1
Measure (pct) Number score Letter grade Disposition
1% 40 A
Pass
6% 36 B
Pass
10% 32 C
Marginal
Substd
Fail- HOLD

[Rich P]

Very simple. You create two tables and 2 queries. I will assume you
are grading students for this explanation. tblStudent contains Student
Information. tblScores contains grading information.

tblScores
ScorePrecent Grade
100 A
99 A
... A
90 A
89 B
88 B
... B
80 B
...

tblStudent
Name Score Total
smith 40 50
jones 38 50
Lee 49 50
...

For calculating the ScorePercent of each student create a query based on
the student table. In the student query, ScorePerent would be a
calculated field which you would include like this:

Name Score Total ScorePercent:(S core/Total) * 100

name this query qryStudentInfo

Now create a 2nd query where you join qryStudentInfo with tblScores on
the common field of ScorePercent which is a unique field in tblScores.
In this query you include the Grade field from tblScores. Now your
query table looks like this

qryStudentScore s
Name Score Total ScorePercent Grade
smith 40 50 80 B
jones 38 50 76 C
...
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bas Cost Budde]

Fred wrote:

I am trying to come up with a way to lookup values in Access as easily as I
can in Excel.

My application is looking up scores. An example lookup table below explains
what I'm after:
TABLE1
Measure (pct) Number score Letter grade Disposition
1% 40 A
Pass
6% 36 B
Pass
10% 32 C
Marginal
Substd
Fail- HOLD

In Excel it is simple. I evaluate a measure say 7%
Excel looks down the table and stops at the 6% line since7% is larger than
6% but smaller than 10%
Excel returns 32 or C or Marginal depending on which place I specify

Example (vlookup(0.07, TABLE1,3) yields a "C" vlookup(0.07, TABLE1,4) yields
"Marginal"

How can I accomplish the same end in Access without getting into
programming?

You can't. Anything is programming here. ;-) But this kind of 'program'
is called a query. I think its SQL looks somewhat like

select top 1 [number score] from table1 where measure>=7 order by measure

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl