Consider the two tables below :

Model | Make | Color
-------|-------|------
FG1    |   2   | B
SRX    |   1   | Y
GR7    |   3   | G

Index | Code | Value
-------|-------|------
1    |   1   | Ford
1    |   2   | Buick
1    |   3   | Chrysler
2    |   B   | Blue
2    |   G   | Green
2    |   Y   | Yellow

What would be the best way to get the following output :

Model | Make | Color
-------|-------|------
FG1    | Buick | Blue
SRX    | Ford | Yellow
GR7    | Chr.. | Green


I tried to figure it out using SQL (Add Command), but telling it to use the same originating field to populate two different fields depending on the index field has proven to be a bit difficult.

Any ideas?

Sorry for the table formatting (lack thereof). It looked good when I typed it.

Edited by delstar - 29 Jun 2009 at 12:36pm

*bangs head on wall*

I was going down that path, but didn't know how to separate the two fields in the SELECT area. I then realized the JOINed tables could be referenced in that section by their alias.

Thanks