linking up fields in three tables.

[Former Member]

Post Author: bpnchan

CA Forum: General

Hi everyone,

It susposes to be a very simple task but I still can't working it out from my research on books and the web. I must miss some very fundamental concept here.

What I am trying to do is to generate a report with less than ten tables. Evrything go well until I come to this point. There are three tables involved in this mess, here is the story:

First one stored the fields PERSON_ID, SURNAME, and GIVENNAME.

Second one stored the fields CONTACT_ID, PERSON_ID

Third on stored the fields PAYED_BY_ID, ALLOCATED_TO_ID, & RECEIVE_BY_ID

These three fileds from the third table should refer to the CONTACT_ID, then to the PERSON_ID and we got the name for the individual of the CONTACT_ID.

I started with linking up RECEIVE_BY_ID with CONTACT_ID and linking up CONTACT_ID with PERSON_ID. It work fine and I can get the name for the recipient. Then I start to link up the other two together with the CONTACT_ID, I don't have anything resulted from the link. I know the "link options" can be where my solution laid. However, after I tried a few combination of "Join type" and "Enforce join" that make sense, I claimed no success from my effort.

Any suggestion?

Brian

[Former Member]

Post Author: bpnchan

CA Forum: General

I feel like I am such a dump person when I worked this out. I simply put Second table twice in at the database expert as an alias, then I can refer to them seperately. It work fine for me.

Brian