Richard,
I have a table of doctors and a table of the doctors referrals to our department.  For years Dr Smith worked out of AAA Orthopedics.  Months ago he left AAA, moved out of state to aother practice. We are again receiving referrals from him.
How do I differentiate between his referrals from AAA and his new location?
In my Excel spreadsheet (which I am now preparing to become an Access database) I list him teice--as "Dr. Smith" and again as "Dr.Smith After AAA".  This seems pretty dopey to do in Access.
Any sugestions?
Thank you,
Don

---

Reply from Richard Rost:

If a doctor will always work at one AND ONLY ONE company/office, then you should set up a DoctorT table, and then a separate CompanyT table to track the companies they work for. Make a CompanyID field in the DoctorT table so you can tell which company the doctor currently works for.

Now, when you create a referral, you'll pick the Doctor from a drop-down list, and you can have his current COMPANY automatically populate on your referral form. You'll store BOTH the company and the doctor IDs in your referral table so you can tell them apart.

This is very similar to the technique that I use for grabbing address info from customers when you create an invoice for them. You store the address information in the customer table, and then when you pick a customer in your invoice form, the address data automatically populates on the order form. See Access 302 for details.

Now, if a doctor can work at MULTIPLE offices/companies, then the situation might get a little trickier:

The BEST way to handle this is two have THREE tables in a many-to-many relationship to track your doctors and the companies they work for: DoctorT, CompanyT.

Now you'll need a cross-reference table to link them together (DoctorXCompanyT) so you can set up relationships like:

Dr Smith, AAA Ortho
Dr Smith, XXX Family
Dr Jones, AAA Ortho
etc.

This is a many-to-many relationship that I cover in Access 309. You'll then make a form to edit your doctor information, and have subform listing all of the companies (offices) he works out of.

Now, when you generate your list of doctors, you need to create a query showing every doctor with every company, or use the TWO combo box trick where you pick a company first, then the second combo box populates with just the doctors from that company.

In either case, you're going to need to store BOTH the doctor and the company ID in your referral form.