I am a recruiter who place IT professionals with clients. I have three tables in my database (people, companies and job order).

I would like to enter the first and last name of the hiring manager in the detail form of the job order form but want it to add to the people table, thus I don't have to enter them once in the job order and once in the people table.

All relationship go from the company table to the job order table and to the people table and are one company to many people and many job orders. So what am I doing wrong. I have foreign keys in each of the other tables.

Try to use Value list from People table field Name&Surname to insert in Jobs table.

Or upload sample file - not clear where table Companies go.

Obviously you understand the relationships, however what you are not realising is that you should be SELECTING an EXISTING record from "people" when creating the "job".

All you need to do is create a script to allow you to create the NEW "people" on the fly (switch to the "people" layout, create record, return to original layout) - then your new person will be available to select from.

You should be using a VALUE LIST to display the "persons".

I am going to try to clarify the problem a little more. Here is the situation.

A company can have many people and many opening.

People can be a match for many companies and many openings.

So far I am keeping the following info in the people table:

First, last name

Employer

Title

work phone, home and cell

Personal address

In the company table I am keeping:

Name of company

Main phone number

URL

Decription of company

When I get an order I want to be able to automatically check whether the company exist or otherwise enter it. I want the contact at the company automatically entered.

The same is true when I enter a person. I want to automatically enter the company.

I would appreciate any help. This is beginning to drive me crazy. I hope that some of you have a better idea. I am enclosing a screen shot of the relationships.

Edited September 29, 200916 yr by Guest