Bin type lookup in access similar to vlookup?

[Fred]

I have an application that involves calculating a score based on a number of
defects. Simplified example of the scoring:

No_Defects Score
1-3 A
4-15 B
16-25 C
over 25 X

In Excel this is easy using a vlookup with its bin system

No_Defects Score
0 A
4 B
16 C
25 X

In Excel, if No_defects=5, it will search down column 1 and retrieve the
score corresponding to the last number before the 5, in this case, "B".

I cannot find a way to do this in Access. The closest I have come is to
create a table with all possible numbers of defects and corresponding scores
but this is not workable, especially when considering decimal numbers.

Is there an elegant way to do this using a function or am I pounding my head
against a wall?

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! =-----

[Terry Kreft]

What score if there are no defects?

Something like

Table DefectsScoring
3 columns
Score Lo Hi
A 1 3
B 4 15
C 16 25
X 25 9999999

PARAMETERS thisscore Short;
SELECT Score
FROM DefectsScoring
WHERE ((Lo<=[thisscore]) AND (Hi >=[thisscore]));

Or
2 Columns
Score Hi
A 3
B 15
C 25
X 9999999

SELECT TOP 1 DefectsScoring.Score
FROM DefectsScoring
WHERE Hi>=[thisscore]
ORDER BY DefectsScoring.Hi;

--
Terry Kreft
MVP Microsoft Access
"Fred" <fr**************@wymans.com> wrote in message
news:40********@corp.newsgroups.com...

I have an application that involves calculating a score based on a number of defects. Simplified example of the scoring:

No_Defects Score
1-3 A
4-15 B
16-25 C
over 25 X

In Excel this is easy using a vlookup with its bin system

No_Defects Score
0 A
4 B
16 C
25 X

In Excel, if No_defects=5, it will search down column 1 and retrieve the
score corresponding to the last number before the 5, in this case, "B".

I cannot find a way to do this in Access. The closest I have come is to
create a table with all possible numbers of defects and corresponding scores but this is not workable, especially when considering decimal numbers.

Is there an elegant way to do this using a function or am I pounding my head against a wall?

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! =-----

[Fred]

Thank you- it's making more sense this way. Is there an easy way to
incorporate this in an unbound control in a form?

Fred

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:DL********************@karoo.co.uk...

What score if there are no defects?

Something like

Table DefectsScoring
3 columns
Score Lo Hi
A 1 3
B 4 15
C 16 25
X 25 9999999

PARAMETERS thisscore Short;
SELECT Score
FROM DefectsScoring
WHERE ((Lo<=[thisscore]) AND (Hi >=[thisscore]));

Or
2 Columns
Score Hi
A 3
B 15
C 25
X 9999999

SELECT TOP 1 DefectsScoring.Score
FROM DefectsScoring
WHERE Hi>=[thisscore]
ORDER BY DefectsScoring.Hi;

--
Terry Kreft
MVP Microsoft Access
"Fred" <fr**************@wymans.com> wrote in message
news:40********@corp.newsgroups.com...

I have an application that involves calculating a score based on a

number of

defects. Simplified example of the scoring:

No_Defects Score
1-3 A
4-15 B
16-25 C
over 25 X

In Excel this is easy using a vlookup with its bin system

No_Defects Score
0 A
4 B
16 C
25 X

In Excel, if No_defects=5, it will search down column 1 and retrieve the
score corresponding to the last number before the 5, in this case, "B".

I cannot find a way to do this in Access. The closest I have come is to
create a table with all possible numbers of defects and corresponding

scores

but this is not workable, especially when considering decimal numbers.

Is there an elegant way to do this using a function or am I pounding my

head

against a wall?

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! =-----

-----= 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! =-----

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another way to state the comparison in the WHERE clause is:

WHERE [thisscore] BETWEEN Lo AND Hi

===

In a form control you could put a domain function in the control's
ControlSource property. E.g. (ignore line-wrap):

=DLookup("Score", "DefectsScoring", Form!ThisScore & "BETWEEN Lo AND
Hi")

The Form!ThisScore is a reference to the control "ThisScore" on the same
form as the control w/ the DLookup() function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQBc34echKqOuFEgEQKpdACdFJRaHcsRoAT5KClPDcgu1Y fBKJUAoNm4
DTCKPtfDRZ3UmBHQXYvWuiAo
=9inO
-----END PGP SIGNATURE-----
Fred wrote:

Thank you- it's making more sense this way. Is there an easy way to
incorporate this in an unbound control in a form?

Fred

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:DL********************@karoo.co.uk...

What score if there are no defects?

Something like

Table DefectsScoring
3 columns
Score Lo Hi
A 1 3
B 4 15
C 16 25
X 25 9999999

PARAMETERS thisscore Short;
SELECT Score
FROM DefectsScoring
WHERE ((Lo<=[thisscore]) AND (Hi >=[thisscore]));

Or
2 Columns
Score Hi
A 3
B 15
C 25
X 9999999

SELECT TOP 1 DefectsScoring.Score
FROM DefectsScoring
WHERE Hi>=[thisscore]
ORDER BY DefectsScoring.Hi;

--
Terry Kreft
MVP Microsoft Access

< snip original post >