Unique Values In Combo Box???

bacarat

Registered User.
Local time
Today, 03:09
Joined
Mar 27, 2007
Messages
106
Hi,
I am trying to get a Combo Box to display only unique values of the "ReferralID" column. However it does not seem to be working with the DISTINCT function. Any help would be great! Below is my SQL:

Code:
SELECT DISTINCT tblReferrals.ReferralID, tblDoctorName.DoctorLastName, tblReferrals.ReferralDate, tblReferrals.ReferralReason, tblReferrals.PatientID, tblReferrals.ClinicID, tblAppointments.StatusID, tblAppointments.AppointmentTypeID
FROM (tblDoctorName INNER JOIN tblReferrals ON tblDoctorName.DoctorID = tblReferrals.DoctorID) INNER JOIN tblAppointments ON tblReferrals.ReferralID = tblAppointments.ReferralID
WHERE ((([tblAppointments]![StatusID]=[Forms]![frmPatientSearch]![cboStatusSearch] Or [Forms]![frmPatientSearch]![cboStatusSearch] Is Null)=True) AND (([tblAppointments]![AppointmentTypeID]=[Forms]![frmPatientSearch]![cboAppointmentSearch] Or [Forms]![frmPatientSearch]![cboAppointmentSearch] Is Null)=True) AND (([tblReferrals]![ClinicID]=[Forms]![frmPatientSearch]![cboClinicSearch] Or [Forms]![frmPatientSearch]![cboClinicSearch] Is Null)=True) AND (([tblreferrals]![PatientID]=[Forms]![frmPatientSearch]![cboPatientSearch] Or [Forms]![frmPatientSearch]![cboPatientSearch] Is Null)=True));
 
The DISTINCT will apply to all fields in the SELECT clause, so that includes doctor's name, referral date, etc.
 
Ok, So if I wanted the DISTINCT clause to only apply to referralID how would I correctly write the SQL?
 
SELECT DISTINCT ReferralID
FROM tblReferrals

As soon as you add the other fields, Access doesn't know which record you want that data from, so it expands the DISTINCT to include them. An alternative would be a totals query, where you could group by the id and use max, min, etc on the other fields.
 
My question is similar to the original posters. I understand distinct works on all the fields in the select set but when I can't figure out why the following query works with a listbox but won't display any rows on a combo:

Code:
SELECT DISTINCT tblCases.dtmCasePreTrialDate FROM tblCases WHERE (((tblCases.dtmCasePreTrialDate) Is Not Null)) ORDER BY tblCases.dtmCasePreTrialDate DESC;

In order to get any data for the combo box, I have to add the unique key for the table to the select set which breaks my ability to only see distinct dates.
 
Found the problem, when the wizard created the control, it created the control with 2 columns and hid the first column (the key in the table). When I manually changed the select statement, I missed the need to change the combo to single column, and to change the space for the first field to the full width of the control.
 
Hello,

Im also having a problem with multiple values in ComboBox. I have used DISTINCT and also tried Group By in the SQL statement, but for some reason the duplicates still show.
Does anyone have any ideas?

Thanks
 
Thanks for looking Fear...

SELECT DISTINCT REF_Agent_Data.[UN Code], REF_Agent_Data.Sub_Agent, REF_Agent_Data.Contact, REF_Agent_Data.Tel, REF_Agent_Data.Email, REF_Agent_Data.Type, REF_Agent_Data.[AG Code]
FROM REF_Agent_Data
GROUP BY REF_Agent_Data.[UN Code], REF_Agent_Data.Sub_Agent, REF_Agent_Data.Contact, REF_Agent_Data.Tel, REF_Agent_Data.Email, REF_Agent_Data.Type, REF_Agent_Data.[AG Code]
ORDER BY REF_Agent_Data.[UN Code];
 
Firstly you do not need a group by. Your SQL is quite straight forward and as far as I can see you will only get duplicates if you have duplicate data. As your SQL Statement has numerous output fields the DISTINCT clause is inappropriate.
 
Thanks - Will have a look through it all and see if I can work it out.
 

Users who are viewing this thread

Back
Top Bottom