Lookup Table

[gems]

I am using crystal 10 and SQL Server 2000 database.

We've been asked to add a new field to our tables so that the users can run the reports based on that new field.
This new field is based on 3 existing fields in the table.
I created a crosswalk table that has all the mappings between the existing fields and the new field.

CrossWalk table

Here is some sample data for the crosswalk-

- If Region is "AL", Sector is "2E", Subsector is "H" then the value in the new field should be "WWY1V"

- Region Sector Subsector New Field
---AL-----2H-----H---------WWY1D
---EA-----1F-----J---------WEW1C
---EA-----1B-----B---------WEW2E

Existing Table
- Region Sector Subsector Type
---AL-----2H-----H---------AAA
---AL-----2H-----H---------BBB
---AL-----2H-----H---------CCC
---EA-----1F-----J----------AAA

User enters 'WWY1D' in the parameter field

Result should show the first 3 records from the Exisiting table.

Results -
- Region Sector Subsector Type
---AL-----2H-----H---------AAA
---AL-----2H-----H---------BBB
---AL-----2H-----H---------CCC


My reports are baed on views. I created a new view in which I created an inner join between the exisiting view and the crosswalk table using the Region, Sector and subsector field as that's the common field but it did not return the right result.

How should the join be done?

Thanks

-E