look up table

Post Author: Colette

CA Forum: Data Connectivity and SQL

I am a new crystal report user. I have crystal reports XI and I connect to my database as ODBC. The way our software database is setup is that one table has the patient name, admission date, discharge date, and status. The status field only shows alpha info such as A, D, T, N, and Z. There is another table in the database that is used as a look up table and there is nothing to link the two tables together. All it has is the following fields Type, code, description. What I want to do is match up the status field with the description for my report.

Here is my report that I have generated so far:

Patient Name Admission Date Inactive Date Length of Stay Status

John Doe 06/01/2007 06/30/2007 30 T

What I would like it to look like:

Patient Name Admission Date Inactive Date Length of Stay Status

John Doe 06/01/2007 06/30/2007 30 Terminated

The other table with the description looks like:

Type: Code Description

PMT

9

Other

PMT

A

Invoice Adjustments/Outliers

POT

1

40/Hrs Per Week

POT

2

No Overtime Calculations

PST

A

Active

PST

D

Deceased

PST

H

Hospitalized

PST

I

Intake

PST

N

Never Serviced

PST

T

Terminated/Discharged

PST

Z

Nursing Home

RAC

1

Native American/AlaskaNative

RAC

2

Asian

RAC

3

Black/Afro American

RAC

4

Hispanic

RAC

5

Native Hawaiian/Pacific Island

I need the type PST to match up the code with status to get the description.

Any thoughts or help would be appreciated.