Problem with 'Lookup'

[Tansar]

Hi,
I have 3 tables, tblCompany, tblBranch & tblJobs all linked together. 1 Company to many Branches and 1 Branch to many jobs relationship.

The problem is that when I create a lookup field in tblJobs to look up the company (for easy insertion) I get a number instead, rather than the Company name and field changes to ID (which is from the tblCompany primary key. Is there a way where I can have only the company showing?

I can get this by removing the ID field from (tblCompany) so now all Company name are in as primary keys with no other field. Is this correct or will it cause me problems later on?

TIA

 

[dcx693]

Short of using the Company name instead of ID as the foreign key field (which I don't suggest) you need to create a multi-column combo (or list) box on a form. With that, you can control which column(s) get display as well as which columns get stored in your data. You can display company name while actually storing the company ID.

 

[Pat Hartman]

You don't need to store the CompanyID in the jobs table. You can obtain it by joining to the Branch table and through that to the Jobs table.