Confusion on how to get the data to query properly.

[Bayou BoB]

I have a table of patients, whereby you can record up to 3 doctors
working on that patient's care, as well as some diagnosis information,
etc. My problem so far has been this. When I go to query the data from
the table, I can get only the first of the 3 doctors to actually show
up in the query by name....the remaining two actually are still shown
as numbers. I'll illustrate below.

tblPatient
Name
DOB
Doctor1
Doctor2
Doctor3
DiagnosisPri
DiagnosisSec
DiagnosisOth

The Doctor's name is being selected from the list of doctors being
housed in another table.

tblDoc
DrName

When it goes into the table from the form, it goes in much as it's
supposed to. The table is all a series of numbers for storing data
more wisely.

Now when I go to make a query to just list the patient and his
doctors, I get results that look something like this.

Patient James Wilson, Doctor1 ODonnell Doctor2 23 Doctor3 18

If I add the DrName 3 times to the query, it still only picks up the
first Doctor1 field, and repeat's that Doctor's name 3 times through
the query, regardless of what number appears with Doctor2 and Doctor
3. How Can I make a query that will actually show something like this
below..

Patient James Wilson, Doctor1 ODonnell, Doctor2 Wheeler, Doctor3
Jamieson...?

This is stumping me what I'm doing wrong!!

 

[John Vinson]

I have a table of patients, whereby you can record up to 3 doctors
working on that patient's care, as well as some diagnosis information,
etc. My problem so far has been this. When I go to query the data from
the table, I can get only the first of the 3 doctors to actually show
up in the query by name....the remaining two actually are still shown
as numbers. I'll illustrate below.

tblPatient
Name
DOB
Doctor1
Doctor2
Doctor3
DiagnosisPri
DiagnosisSec
DiagnosisOth

Well... stop.

You have a Many to Many relationship between Patients and Doctors. A
patient might have FOUR doctors, and six diagnoses. The proper
structure for this would be to have two more tables - PatientDoctor
with the patient's unique ID (Name and DOB are NOT guaranteed to be
unique!) and the DOctorID, with one *row* per doctor assigned to a
given patient.

The Doctor's name is being selected from the list of doctors being
housed in another table.

tblDoc
DrName

When it goes into the table from the form, it goes in much as it's
supposed to. The table is all a series of numbers for storing data
more wisely.

You're evidently yet another victim of Microsoft's misdesigned and
misleading Lookup Wizard. See
http://www.mvps.org/access/lookupfields.htm for a critique.

Now when I go to make a query to just list the patient and his
doctors, I get results that look something like this.

Patient James Wilson, Doctor1 ODonnell Doctor2 23 Doctor3 18

Dr. O'Donnell's ID is actually stored in your table too. That basic
fact is concealed from your view by the misbegotten Lookup field.

If I add the DrName 3 times to the query, it still only picks up the
first Doctor1 field, and repeat's that Doctor's name 3 times through
the query, regardless of what number appears with Doctor2 and Doctor
3. How Can I make a query that will actually show something like this
below..

Patient James Wilson, Doctor1 ODonnell, Doctor2 Wheeler, Doctor3
Jamieson...?

With this wide-flat design you'll need to add the Doctor table to the
query THREE TIMES - joining Doctor1 to DoctorID in the first instance,
Doctor2 to DoctorID in the second, and Doctor3 to DOctorID in the
third. With the normalized design you can have a Combo Box in a
Subform bound to PatientDoctor, and see the one, two, three or five
doctors on separate rows in the subform.