Linking multiple fields to 1 field

[Viscount]

I am new to this - have had CR for less than a week and I am already running into problems..!

In a table each record contains (amongst other things) 6 fields each containing a different code. The code corresponds to a different table with 2 fields - one being the code and the other the description.

what I want to do is link these fields and tables together and get the results. My problem is that the in the first table there are null values. CR is returning nothing!

What do I have to do to be able to return a record and the text description of the code for records with 1,2,3,4,5 or 6 codes?

Thanks.

 

[MJRBIM]

What you are looking to do is create Left-Outer links from you RECORD table to multiple copies of your CODE DESCRIPTION table.

Add the CODE DESCRIPTION table to the report from the menu bar

Database -> Add Database To Report

It will prompt you to make an Alias of the table. You will want to do this six times, once for each of the Codes in your RECORD table.

Then link RECORD.Code1 ---> CODE DESCRIPTION.Code
RECORD.Code2 ---> CODE DESCRIPTION_alias2.Code
RECORD.Code3 ---> CODE DESCRIPTION_alias2.Code
RECORD.Code4 ---> CODE DESCRIPTION_alias2.Code
RECORD.Code5 ---> CODE DESCRIPTION_alias2.Code
RECORD.Code6 ---> CODE DESCRIPTION_alias2.Code

It will let you see the data like this.


RECORD CODES DESCRIPTION
---------------------------------------------------------------------------------


RECORD 1 CODE 1 ABC Code ABC Description
CODE 2 DEF Code DEF Description
CODE 3 HIJ Code HIJ Description
CODE 4 KLM Code KLM Description
CODE 5 NOP Code NOP Description
CODE 6 QRS Code QRS Description
---------------------------------------------------------------------------------

RECORD 2 CODE 1
CODE 2
CODE 3
CODE 4
CODE 5
CODE 6 ABC Code ABC Description
---------------------------------------------------------------------------------

RECORD 3 CODE 1
CODE 2
CODE 3
CODE 4
CODE 5 NOP Code NOP Description
CODE 6
---------------------------------------------------------------------------------

RECORD 3 CODE 1
CODE 2 DEF Code DEF Description
CODE 3
CODE 4 KLM Code KLM Description
CODE 5
CODE 6 QRS Code QRS Description
---------------------------------------------------------------------------------

RECORD 4 CODE 1 ABC Code ABC Description
CODE 2
CODE 3 HIJ Code HIJ Description
CODE 4
CODE 5 NOP Code NOP Description
CODE 6
---------------------------------------------------------------------------------

RECORD 5 CODE 1
CODE 2 DEF Code DEF Description
CODE 3
CODE 4 KLM Code KLM Description
CODE 5
CODE 6 QRS Code QRS Description
---------------------------------------------------------------------------------

RECORD 6 CODE 1
CODE 2 KLM Code KLM Description
CODE 3
CODE 4
CODE 5
CODE 6
---------------------------------------------------------------------------------

 

[MJRBIM]

Ooooops....typo!!

The above item should read.....

Then link RECORD.Code1 ---> CODE DESCRIPTION.Code
RECORD.Code2 ---> CODE DESCRIPTION_alias2.Code
RECORD.Code3 ---> CODE DESCRIPTION_alias3.Code
RECORD.Code4 ---> CODE DESCRIPTION_alias4.Code
RECORD.Code5 ---> CODE DESCRIPTION_alias5.Code
RECORD.Code6 ---> CODE DESCRIPTION_alias6.Code

 

[Viscount]

Thanks - that makes a bit of sense now.

V.