Combo box shows duplicate records

[Kurt]

I have two synchronized combo boxes so that when a
patient is selected in the first box, a second combo box
is requeried so that it lists only cases for that
patient. (A patient can have one or more cases.) The user
can then select a case in the second combo box to pull up
the record.

If the patient has one case, then that one case is listed
in the second combo box. Great. But if the patient has,
for example, two cases, *four* items are listed in the
second combo box (two instances of each case). I've tried
playing around with DISTINCT vs DISTINCT ROW to no avail.
How can avoid these duplicates?

The first combo box (cboFindPatient) is based on:

SELECT tblPatients.PatientName, tblPatients.PatientID
FROM tblPatients
ORDER BY tblPatients.PatientName;

The second combo box (cboFindCase) is based on:

SELECT DISTINCT tblCases.CaseNumber,
tblCases.CaseDate, qryMedicalIntake.PatientID,
qryMedicalIntake.*
FROM qryMedicalIntake INNER JOIN tblCases ON
qryMedicalIntake.PatientID = tblCases.PatientID
WHERE
(((qryMedicalIntake.PatientID)=[Forms]!
[frmMedicalIntake]![cboFindPatient]))
ORDER BY tblCases.CaseNumber;

Thanks. - Kurt

 

[Marshall Barton]

I have two synchronized combo boxes so that when a
patient is selected in the first box, a second combo box
is requeried so that it lists only cases for that
patient. (A patient can have one or more cases.) The user
can then select a case in the second combo box to pull up
the record.

If the patient has one case, then that one case is listed
in the second combo box. Great. But if the patient has,
for example, two cases, *four* items are listed in the
second combo box (two instances of each case). I've tried
playing around with DISTINCT vs DISTINCT ROW to no avail.
How can avoid these duplicates?

The first combo box (cboFindPatient) is based on:

SELECT tblPatients.PatientName, tblPatients.PatientID
FROM tblPatients
ORDER BY tblPatients.PatientName;

The second combo box (cboFindCase) is based on:

SELECT DISTINCT tblCases.CaseNumber,
tblCases.CaseDate, qryMedicalIntake.PatientID,
qryMedicalIntake.*
FROM qryMedicalIntake INNER JOIN tblCases ON
qryMedicalIntake.PatientID = tblCases.PatientID
WHERE
(((qryMedicalIntake.PatientID)=[Forms]!
[frmMedicalIntake]![cboFindPatient]))
ORDER BY tblCases.CaseNumber;

It looks like that query is going to return "duplicate"
records for every Intake with the PatientID.

I don't see what the Intake table has to do with this
operation. Try removing it from the query.

 

[Kurt]

I don't see what the Intake table has to do with this

operation. Try removing it from the query.

The intake table (tblMedicalIntake) is important because
the main form is based on a query which uses the intake
table.

The medical intake form is based on qryMedicalIntake:

SELECT tblPatients.PatientID, tblPatients.PatientName,
tblCases.CaseNumber, tblCases.CaseDate,
tblMedicalIntake.*
FROM tblPatients INNER JOIN (tblCases LEFT JOIN
tblMedicalIntake
ON tblCases.CaseNumber = tblMedicalIntake.CN)
ON tblPatients.PatientID = tblCases.PatientID;

Basically, patients (stored in tblPatients) and their
cases (stored in tblCases) are created and edited in
another database. Medical Intake records, however, are
created and edited in this database (which has
tblPatients and tblCases as *linked* tables). There is a
one-to-many relationship between tblPatients and
tblCases, and a one-to-*one* relationship between
tblCases and tblMedicalIntake.

A medical intake record can only be created for patients
with cases who have already been entered in the other
database. So, I need the combo boxes to show all patients
and all cases, whether or not they have a medical intake
record (stored in tblMedicalIntake). If a medical intake
record has not been created for a given patient/case,
pulling up the name and case, and then entering data into
the form will create the intake record for that patient's
case in tblMedicalIntake.

-----Original Message-----

I have two synchronized combo boxes so that when a
patient is selected in the first box, a second combo box
is requeried so that it lists only cases for that
patient. (A patient can have one or more cases.) The user
can then select a case in the second combo box to pull up
the record.

If the patient has one case, then that one case is listed
in the second combo box. Great. But if the patient has,
for example, two cases, *four* items are listed in the
second combo box (two instances of each case). I've tried
playing around with DISTINCT vs DISTINCT ROW to no avail.
How can avoid these duplicates?

The first combo box (cboFindPatient) is based on:

SELECT tblPatients.PatientName, tblPatients.PatientID
FROM tblPatients
ORDER BY tblPatients.PatientName;

The second combo box (cboFindCase) is based on:

SELECT DISTINCT tblCases.CaseNumber,
tblCases.CaseDate, qryMedicalIntake.PatientID,
qryMedicalIntake.*
FROM qryMedicalIntake INNER JOIN tblCases ON
qryMedicalIntake.PatientID = tblCases.PatientID
WHERE
(((qryMedicalIntake.PatientID)=[Forms]!
[frmMedicalIntake]![cboFindPatient]))
ORDER BY tblCases.CaseNumber;

It looks like that query is going to return "duplicate"
records for every Intake with the PatientID.

I don't see what the Intake table has to do with this
operation. Try removing it from the query.

 

[Marshall Barton]

I think I understand(?), but you did say "I need the combo
boxes to show all patients and all cases, whether or not
they have a medical intake record", so why does the combo
box need the Intake table?

It makes sense for the form, but I don't see how the combo
box needs it. If you really do have a reason for Intake
data in the combo box, then only select the fields you are
going to use. Maybe then you can narrow it down enough that
DISTINCT will get rid of the duplicates.
--
Marsh
MVP [MS Access]

I don't see what the Intake table has to do with this
operation. Try removing it from the query.

The intake table (tblMedicalIntake) is important because
the main form is based on a query which uses the intake
table.

The medical intake form is based on qryMedicalIntake:

SELECT tblPatients.PatientID, tblPatients.PatientName,
tblCases.CaseNumber, tblCases.CaseDate,
tblMedicalIntake.*
FROM tblPatients INNER JOIN (tblCases LEFT JOIN
tblMedicalIntake
ON tblCases.CaseNumber = tblMedicalIntake.CN)
ON tblPatients.PatientID = tblCases.PatientID;

Basically, patients (stored in tblPatients) and their
cases (stored in tblCases) are created and edited in
another database. Medical Intake records, however, are
created and edited in this database (which has
tblPatients and tblCases as *linked* tables). There is a
one-to-many relationship between tblPatients and
tblCases, and a one-to-*one* relationship between
tblCases and tblMedicalIntake.

A medical intake record can only be created for patients
with cases who have already been entered in the other
database. So, I need the combo boxes to show all patients
and all cases, whether or not they have a medical intake
record (stored in tblMedicalIntake). If a medical intake
record has not been created for a given patient/case,
pulling up the name and case, and then entering data into
the form will create the intake record for that patient's
case in tblMedicalIntake.

-----Original Message-----

I have two synchronized combo boxes so that when a
patient is selected in the first box, a second combo box
is requeried so that it lists only cases for that
patient. (A patient can have one or more cases.) The user
can then select a case in the second combo box to pull up
the record.

If the patient has one case, then that one case is listed
in the second combo box. Great. But if the patient has,
for example, two cases, *four* items are listed in the
second combo box (two instances of each case). I've tried
playing around with DISTINCT vs DISTINCT ROW to no avail.
How can avoid these duplicates?

The first combo box (cboFindPatient) is based on:

SELECT tblPatients.PatientName, tblPatients.PatientID
FROM tblPatients
ORDER BY tblPatients.PatientName;

The second combo box (cboFindCase) is based on:

SELECT DISTINCT tblCases.CaseNumber,
tblCases.CaseDate, qryMedicalIntake.PatientID,
qryMedicalIntake.*
FROM qryMedicalIntake INNER JOIN tblCases ON
qryMedicalIntake.PatientID = tblCases.PatientID
WHERE
(((qryMedicalIntake.PatientID)=[Forms]!
[frmMedicalIntake]![cboFindPatient]))
ORDER BY tblCases.CaseNumber;

It looks like that query is going to return "duplicate"
records for every Intake with the PatientID.

I don't see what the Intake table has to do with this
operation. Try removing it from the query.