How to show description from other table on multiple ID in same record 1

[fixumdude]

I inherited this database structure so I know it's not normalized like it should be.

I have a report with multiple IDs in the same record that all link to a description table.

in my main table a tray has 8 slots...
trayID = 0001
cookie1 = C
cookie2 = P
cookie3 = M
cookie4 = NULL
cookie5 = NULL
...etc.

We have a cookie table which contains the cookie IDs and their descriptions (e.g.
C,Chocolate Chip
P,Peanut Butter
M,Macadamia Nut

I would like to show the friendly name on the report for each cookie slot instead of the ID.

When I link the tray table to the cookie table on the cookie ID, it will show the first cookie name, but none of the others.

I can get this...

[b]Tray    Cookie1          Cookie2          Cookie3[/b]
0001    C                P                M

or this...

[b]Tray    Cookie1          Cookie2          Cookie3[/b]
0001    Chocolate Chip

But I would like to display this....

[b]Tray    Cookie1         Cookie2         Cookie3[/b]
0001    Chocolate Chip  Peanut Butter   Macadamia Nut

Is there a way in Crystal Reports to show different fields from another table in the same record?

Either this is very simple or very hard, because I can't find any kind of example in any of my books or online.

Thanks in advance for any direction you can give me.

 

[IanWaterman]

You will have to add your description table in as many times as you have fields describing cookies.

Crystal will complain table already exists just say yes and it will automatically alias as Description_1.

Place the descriptions in columns as you want.

Ian

 

[fixumdude]

IanWaterman,

That worked like a charm! Thanks a million...I'm pretty new to Crystal Reports and I've been working on that one on and off for about a month!!