How to select records form 3 tables ?

[mathew001]

I am having an issue getting records. I have a table X. Table X is having a unique id field. Now say that there are three other tables, Table A, Table B and Table C. These Tables can be linked with Table X using the unique field.

Table A has a tracking status field, Table B has an incident field and Table C has an enforcment field. I need to create a report which gives all the unique ids in Table X
in some way connected Table A, Table B and Table C.

If I join the tables with an equal join, I get only records from Table X which are there in Table A, Table B and Table C. I did a left outer join, which gives ALL the records in Table X. I need all Table X values which are related to either one of Table A, or Table B or Table C.

Thanks !!!

 

[Ngolem]

a numeric example would be useful in sorting out your problem statement.

tell me if this describes your problem. You have a "Unique ID" field common to all 3 tables + Table X...but the value of this field may not exist in all of the tables .... is that your problem???

One approach other than simple linkage is to make 3 subreports...one for Table A, Table B and Table C. These would be linked by the "Unique ID" to your Main report, which is made from Table X.

This way you should get the data you want.

This is about as detailed as I can get based on what you have shown us. Jim Broadbent

 

[mathew001]

Yeah you understood excatly what I was looking for.

Is there any linking option which will solve the issue, without using subreports ? Just curious !!!

Thanks !!!

 

[Cort]

I'm still learning the program but it seems that you could use left outer joins from table X to the other tables then use a formula to have the records returned. Maybe something like:

If [unique ID table X] = [unique ID Table A] then "print info from table X and A you want" else "" (then suppress blank returns)

Write 3 forumlas for each of A B and C each returning the info you want. You could then write a 4th formula to clean up the presentation of the information.

I'm sure someone else will come up with a better solution but that one should work

 

[Ngolem]

so...the table linking would be like this then


Table X Table A

Unique ID -------> Unique ID
field 1 | | field 1a
field 2 | | field 2a
| |
| |
| | Table B
| |
| |-------> Unique ID
| field 1b
| field 2b
|
|
| Table C
|
|-------> Unique ID
field 1c
field 2c

I am still trying to understand what you want reported....Is it the Unique ID you want or values in the tables ABC that have common Unique ID's?
Jim Broadbent