Query shows only one record

[Widmerpool]

I've got a table of patients and a table of family doctors. I've created a
query to bring both sets of data together. A form, based on that query,
allows me to enter up details for a new patient; I select their doctor from a
combo list, and the doctor's details (address, telephone) appear in the
relevant fields on the form. The problem is, I started with a table of 103
patients, but now only one shows up in the query (they're still there in the
underlying table). I haven't yet started entering doctors. I'm guessing I've
done something pretty simple, but I have no idea what!

 

[John Spencer]

It would help if you posted the SQL of the query you are using. The
problem is probably caused by the type of join you have in the query
between the patients table and the doctors table. Try the following:
-- Open the query in design view
-- Double-click on the join line between the tables
-- Select option 2 or 3 whichever one says All records in patient table
and matching records in doctor table
-- Save the changes

By the way, your current structure works as long as you are recording
only one doctor per patient. If you need to record multiple doctors per
patient then you should consider modifying your structure to include
another table that contains fields to record the PatientId, the
DoctorID, and possibly the type of doctor. For instance, I have a
primary care physician, an oncologist, and a cardiologist.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Widmerpool]

I clicked the join and selected option 3 - and everyone appeared! Excellent,
thank you John.

I'll only need one doctor per patient, because we're a community health
facility and we just need to know who the General Practitioner is so we can
report back to them. Our IT department are just about willing to accept us
doing this for mail-merge purposes - anything else and they'd be wanting to
design the database themselves.

 

[John W. Vinson]

I've got a table of patients and a table of family doctors. I've created a
query to bring both sets of data together. A form, based on that query,
allows me to enter up details for a new patient; I select their doctor from a
combo list, and the doctor's details (address, telephone) appear in the
relevant fields on the form. The problem is, I started with a table of 103
patients, but now only one shows up in the query (they're still there in the
underlying table). I haven't yet started entering doctors. I'm guessing I've
done something pretty simple, but I have no idea what!

It's not actually necessary to base this form on a Query joining the two
tables. John's outer-join suggestion may work for you, but you can also simply
base the form on the Patients table, and have a combo box to select the
physician.

To display the doctor's details, you can put unbound textboxes on the form.
Include the fields you want to see in the combo box's row source query, and
put

=cboDoctor.Column(n)

as the control source of textboxes on the form, where cboDoctor is the name of
the combo box control and (n) is the zero based position of the field you want
to see - e.g. if the phone number is the fourth field in the combo box query
use (3).