Choosing 5 codes in one table from a master code table 1

[winston01]

Thank you in advance for your help.

I have a table that consist of 200 codes and their definitions.

In another table I need to choose up to 5 of these codes to define a given task. I want the definition to automatically appear when I choose the code.

I know I need to build a query to pull the definition, but how do I get five fields to pull from the same field in another table? I thought you could just add five fields that all look up to the same table, but this didn't work once I got to the query level to pull the definition. Help!

Thank you.

 

[lespaul]

So you have a list of codes in a table:
[tt]
CODE Description
ATTN Attention
Re Regarding
FR From
ASCN Association
[/tt]

now you say that:

In another table I need to choose up to 5 of these codes to define a given task. I want the definition to automatically appear when I choose the code.

(aside - if you are going to be choosing up to 5 codes, you need a separate record for each choice, you DON'T normally want to store a field like:
SELECTEDCODES: "ATTN, Re, FR, ASCN")

Normally what you do in this situation is use the code in the TABLE and when you need the description you use a JOIN in the query.

Let's say Table2 is:
[tt]
Field1 Field2 Code
ABC 123 ATTN
DEF 345 Re
[/tt]

now in a query you would do:

SELECT Field1, Field2, Description FROM Table2 INNER JOIN Table1 ON Table1.Code = Table2.Code

now your query will return the description that you'll need to work with.

One of the reasons for this is, let's say, in a year the rules of the business change and ATTN now means Attendance. Instead of having to update every field in every table that you've put the description in, all you have to do is update the description in the main code table and it's changed everywhere.

Read the JOIN article linked below for more information on JOINS and how they work.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[winston01]

Thank you for your help and patience. I have been working with this and i am still having problems.

Table one is a master of diagnosis codes
Table two is an exam with 20 fields per record. For one given exam (record) you can have up to 5 diagnosis codes to be chosen from table one.

Can you please help me understand how you would lay this out. I tried three table: Exam linked to Exam diagnosis code linked to diagnosis codes. But I can't get anything to work.

I appreciate your time.

Thank you.

 

[lespaul]

Ok, you're going to need at least four tables:

PatientInfo
PatientID (PK)
Name
Address
(any other information about the OATIENT)

DiagnosisCodes
ICD9Code (PK)
Description

Exam
ExamID (PK)
PatientID (FK)
ExamDate
(any other information about the EXAM)

PatientExamDiagCodes
PEID (PK)
ExamID (FK)
ICD9Code (FK)
(any other information about the DIAGNOSIS, maybe a check to indicate a primary diagnosis as opposed to a secondary?)

Now with this table setup, next year when a decision is made that a patient can have eight diagnosis codes, no change is required to the table structure.

HTH

Leslie