Lookup tables - alternatives

[John M]

Hi,

In my database I am converting grades into scores (A=7, B=6, B/C = 6.5) I'm
told this is quite slow, and sure enough, a report doing a fair bit of this
is slow. I used to have the conversion in the code, but changed it so the
user can edit the table contents and use it for a different set of score
(4a, 4b etc).

Can I achieve the flexibility but recover the speed. As there are no more
than say 12 different score, I wondered about staying with the data in a
table and loadinf it into an array, then accessing this. Would it work? How
would I do it?

Ideas if possible please?

Thanks in advance

[Albert D. Kallal]

If you just place the values in a simple table like:
LetterCode ScoreValue
A 7
B 6
C 6.5

etc. etc.
Now, just build a query that joins in the above table to your existing data
table. (ie: just use the query builder to join in the above table, joined on
letter code). With small tables of 100,000 records, you can join this data
in well under 1 second, so, I don't know why you are having performance
problems.

Perhaps the data tables you are dealing with are in the millions of records?

You mentioned "slow", but make no mention of the size of tables you are
dealing with. Perhaps the tables are too large for ms-access?

Anyway, if your tables are small, in the 100,000 record range...using the
above table with the query builder should add any perceptible delay to your
reports. In other words, if you add the above table to your existing sql
used for the report...you will be HARD pressed to see any reduction in
performance in the report...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Wayne Morgan]

Using the Lookup Table shouldn't be to slow. What you would need is a common
field value in the main table and the Lookup Table. In the example you give,
you would have two fields in the Lookup Table, the first one would contain
A, B, B/C, etc. The second one would contain 7, 6, 6.5, etc. From your
description, I assume the main table would contain the A, B, B/C values.
Use a query to send the data to your report, add both tables to the query
and link them on this common field. Next, place the second field from the
Lookup Table in the query's output. Because of the link, the text grades
will be associated with their equivalent scores.

--
Wayne Morgan
Microsoft Access MVP
"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:cn*******************@news.demon.co.uk...

Hi,

In my database I am converting grades into scores (A=7, B=6, B/C = 6.5)
I'm
told this is quite slow, and sure enough, a report doing a fair bit of
this
is slow. I used to have the conversion in the code, but changed it so the
user can edit the table contents and use it for a different set of score
(4a, 4b etc).

Can I achieve the flexibility but recover the speed. As there are no more
than say 12 different score, I wondered about staying with the data in a
table and loadinf it into an array, then accessing this. Would it work?
How
would I do it?

Ideas if possible please?