Database Normalization - Lookup 
Author Message
 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 report.

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.

EXAMPLE

Main Table

NAME
ADDRESS
...etc
CODED FIELD1
CODED FIELD2
CODED FIELD3
...etc
------------------------------------------
SYSTEM FILE

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
FIELDCODE="WHATEVER"

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
FIELDCODE variable.

Is this a Stored Procedure (SQL) issue?

SQL expressions want work, because sometimes they may be from different ODBC
sources?

Any thoughts or comments would be appreciated.



Sun, 29 Dec 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Who's good at Database Design ? (normalization)

2. Using a lookup value as ControlSource w/o changing the lookup table

3. Trying to do DNS lookup and reverse DNS lookup

4. Database lookup in VB5

5. database lookup on new Contact

6. Database Lookup

7. lookup data from one database and insert into another

8. database lookup using radio buttons

9. A tough one, editable grid with database lookup, real-time data feed and calculation

10. database lookups in ASP

11. database Lookup ?

12. Quickest database lookup

 

 
Powered by phpBB® Forum Software