Database Normalization - Lookup
I have a database that uses a system table to store descriptions for various
codes that are in the main tables. The main table may have 5 fields that are
coded. The code, usually about 4 characters, is all that is stored in the
main table, and is not "human readable". I need to be able to go to the
system table, lookup the code description, and return this description to
The problem is these databases, as mention earlier, have multiple fields
that need the same system table. In addition, the system table requires two
conditions in order to retrieve the description:
1. It needs to know the CODE, of course, from the main table,
2. It needs a FIELDCODE, so it will know it is looking for the code from
step 1, and returning a description, for a particular field.
CODENAME (stored code from CODED FIELD#
DESCRIPTION (the result we want returned to the report)
FIELDCODE (this field tells the lookup to look for CODED FIELD# with
The FIELDCODE makes sure that if two CODED FIELDS contain the same code by
coincidence, that only the correct one is returned.
The problem I am having is that I can't make multiple links (FIELD1, FIELD2,
..etc) to the same system table. And, even if I could, I can't pass the
Is this a Stored Procedure (SQL) issue?
SQL expressions want work, because sometimes they may be from different ODBC
Any thoughts or comments would be appreciated.