Lookup Fields

[Larry Bernstein]

I have just read where it is not the best "methodology" to use Lookup
fields in tables. Could someone help me with a better way of
accomplishing the same thing.

I am a novice to Access and have created a database concerned with
various items relating to the physicians in our group. I have to keep
track of the various hospitals they are affiliated with, their office
locations and the other items associated with those (office manager
name, phone, how many other physicians are their), their specialty, etc.

Larry

 

[Ken Snell]

The reason for not using Lookup fields in tables is because you should
always use forms to handle your data; it's not good practice nor recommended
that you enter data directly into tables (in ACCESS, the fullest error
recovery and validation can be done in forms). When you use a Lookup form in
a table's field, it's often the case that you forget that that field doesn't
actually contain the value that it shows, and then when you try to run
queries using that field you don't get the results that you expect.

Create a form, and bind the form to that table or to a query that uses that
table. Put controls on the form and bind the controls to those fields. Use
the events of the controls and the form to do your validations and other
activities.

 

[Evi]

Lookup fields are tempting. But forms really are better for inputting data
as you will find the longer you use your database.

In your case, you would probably have (at least) a table to contain a list
of physicians, a table to contain the list of hospitals and one for the list
of specialities. I can't tell from your message if the offices belong to the
hospitals or to the physicians and if there is more than one office per
hospital/physician and if a physician can have more than one speciality so
the rest of the db I'm unclear about. You would probably have a table which
links physicians to hospitals (if a physician can belong to more than one
hospital)

So if the Physicians table contained
PhysID (Your primary key field)
PhysFirstName
PhysLastName

etc

then your PhysicianHospital table would have
PHID (Your primary key field)
PhysID
HospID
and stuff to do with that physician in that hospital.

In the Relationships window you would join
You would click on this table in your database then go to Insert, Form. You
would choose the sort of form you wanted.

You would open the form in Design view and put a combo box in it. The combo
box would look up data from your Physicians table and have PhysID and then
the Physician's names. It would 'Store its value' in PhysID.

you would create another combo to choose a Hospital from the Hospital and
store its value in HospID. If you chose to make the form a Datasheet form,
it would look just like a table with Lookups in it but it would have many
more powers.

Evi