Duplicate Entries in Combo Box Problem

[AbeA]

I've got a combo box that lists duplicates. I want the dupes removed.

I went to the Query Builder for my Combo Box and set Unique Values from "No" to "Yes" - Saved, then re-opened the form. It still lists dupes.

I went back into Query Builder and unchecked the box for the Patient_ID (Autonumber) field. I saved it, then viewed the Datasheet for the Query, and it correctly lists the data I want WITHOUT dupes.

However, when I go to Form View, the combo box lists several blank entries..
The entries come back when I re-enable the Autonumber field in the Query.

This is what I currently have...
SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;

 

[MarkK]

But this . . .

SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;

. . . doesn't show dupes does it?

But you have a problem if you have two same Fullnames in . . .

SELECT DISTINCT PatientID, Fullname FROM Patient ORDER BY FullName;

. . . because those dupe Fullnames have distinct ID's. In that case you probably have a normalization problem, or you actually have dupes that you need to remove.

 

[AbeA]

Correct, this:
SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;

Doesn't show dupes, and it lists correctly, only in Datasheet view in the Query Builder, not in the form (it displays blank entries).

Yes, A lot of the dupe full name fields have distinct ID's. So, it appears I have a normalization problem.

Is there code available that will hide the dupes for me? All the duplicate values list the same set of data, it doesn't matter which dupe is selected. (Working on getting the normalization issue resolved, that's a whole other animal.)

 

[MarkK]

Working on getting the normalization issue resolved, that's a whole other animal.

No, the dupe problem in almost certainly a symptom of the normalization problem, so do it right, solve the normalization problem first. My 2c.

 

[AbeA]

Well, I believe I've discovered my Normalization issue, and now I have a new problem. In my "Patients" table, I have several duplicate names listed. Since this table has relationships with three other tables in my database, each patient record in my Patients table has a plus sign next to it that contain all related records from other tables for that patient.

The main form that I use lets you search for a Patient name via combo box, then it lists all the info from the other three tables for that patient in a subform query below it.

When I select a patient record in my main form, then add a new prescription for that patient in my subform, it adds a NEW record for that patient in my Patients table.... What would be the appropriate way to go about repairing this?