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.newsgroups.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:(Score/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

qryStudentScores
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