Linking tables with multiple matching values

[petejigsaw]

Hi

I am working on a report based on a series of tables from a CRM system. My problem is how to show easily on a report the analysis of what parts of an order are faulty and why.

There are 3 tables that are relevant:

csc_item
csc_itemfault
csc_faultcode

csc_item lists the singular item that is faulty, and links to csc_itemfault using a simple inner join.

csc_itemfault then indicates how the fault on the item was analysed with up to 3 fault codes:

prefaultcodeID
secfaultcodeID
terfaultcodeID

Each faulty item must have at least prefaultcodeID set, but most have all three. These are simple integer fields.

To analyse these, the integer fields refer to values and descriptions in csc_faultcode, and this is where I'm stuck.

How do I link csc_itemfault to csc_faultcode in a manner so that I can show, for each single faulty item, all faultcode descriptions?

Simply linking on prefaultcodeID retrieves only the first fault description.......

Thanks in anticipation

 

[IanWaterman]

YOu bring in the table csc_faultcode 3 times and give it an alias. Crystal will warn you that you are adding table in again and alias with csc_faultcode_1

Rename tables in Database expert to make it easier to understand linking.

csc_faultcode_pf
csc_faultcode_sf
csc_faultcode_tf


Then link prefaultcodeID to faultcode in csc_faultcode_pf using a left outer
repeat for
serfaultcodeID to faultcode in csc_faultcode_sf
terfaultcodeID to faultcode in csc_faultcode_tf

Ian

 

[petejigsaw]

Thanks Ian - tried that and it worked fine, so many thanks.