Lookup Source

[adams.bria]

Here is the basic layout of the data:
There are 4 tables,
1. Dept (contains a list of departments) Key: DID
2. Contacts (A list of people who work) Key: CID
3. Dept_Cont (Links CID & DID) Key: DCID
4. Assn (Assigns a DCID to existing data)

What I am wondering is how to make DCID in the Assn table a lookup field. I can make it a lookup field to the point where it shows the CID & DID, however, I want it to show the name of the Dept and the Contact person. So essentially it has to reverse track into two different tables for display data. I have tried making the DID and CID lookup columns in the Dept_Cont table, where it is showing the actual names, however when I then make it a lookup in the Assn table it continues to just show the numeric ID number instead of the names. Anyone have any ideas to try?

Brian

 

[boblarson]

Welcome to AWF...

You should NOT use lookups at Table level. See here for why. You CAN use a combo on a FORM to select the text and store the ID which is what you should do.

 

[adams.bria]

Yes I know a lot of people find them evil but in this specific instance it would suite me well.

Beyond that:
Am I not going to run into the same problem when I create the combo on the form. How can I have the box lookup the name of the contact and the name of the dept when they are in two separate tables?

 

[boblarson]

Beyond that:
Am I not going to run into the same problem when I create the combo on the form. How can I have the box lookup the name of the contact and the name of the dept when they are in two separate tables?

No you won't run into the same problem when you create a combo on the form. And you can get both pieces of data because the combo box on the form's ROW SOURCE can be a query where you pull it all together.

And believe me, if you are going to be doing any reporting or anything with this data, it will cause things to go screwy when you go to show things on the report. Lookups at table level don't play well with reports.