Creating combo box with multiple columns that need to be no duplicates

[JFo]

Probably fairly easy but have been searching online with no luck

For every type of equipment there is many questions that I would like to display when selected


I have created a combo box with multiple values from 2 different tables with the following values

tblQuestions.EquipTypeID, tblEquipmentType.EquipTypeName, tblQuestions.QuestionID

however I want to sort the values so that the combo only displays one selection for each equipment type

The following SQL statement gives a different instance for each question

SELECT tblQuestions.EquipTypeID, tblEquipmentType.EquipTypeName, tblQuestions.QuestionID
FROM tblQuestions INNER JOIN tblEquipmentType ON tblQuestions.EquipTypeID = tblEquipmentType.EquipTypeID;

AS Does this one as all rows are unique it is the equipment type that is the same

SELECT DISTINCT tblQuestions.EquipTypeID, tblEquipmentType.EquipTypeName, tblQuestions.QuestionID
FROM tblQuestions INNER JOIN tblEquipmentType ON tblQuestions.EquipTypeID = tblEquipmentType.EquipTypeID;



I think I may be able to use GROUP BY but nothing that I implement seems to work and have searched far and wide

GROUP BY (tblQuestions.EquipTypeID, tblEquipmentType.EquipTypeName, tblQuestions.QuestionID)


Thanks

[June7]

Don't understand what you are trying to do. What do you mean by 'sort the values so that the combo only displays one selection for each equipment type'?

[JFo]

That is precisely what I would like to do. Filter the combo box based on the SQL statement so that it does not give me any repeats for equipment type. Notably the EquipTypeID is fk in tblQuestions. I want to display the EquipmentName in the combo box but only once (per name/ equipment type) which is associated with many different questions

Thanks

[June7]

Would have to view your data, but I suspect the reason DISTINCT doesn't work is that by including the QuestionID in the query, each record is unique.

[JFo]

Yes that is correct distinct does not work as the QuestionID keeps changing

Example data

tblQuestions.QuestionID - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11... etc
tblQuestions.EquipTypeID - 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2...etc (foreign key from tblEquipmentType
tblEquipmentType.EquipmentName- digger, digger, digger, digger, digger, toolbox, toolbox, toolbox, toolbox, toolbox, toolbox...etc

Thanks

[June7]

My point is: The RowSource list of EquipmentID cannot be unique as long as the QuestionID is included in the SQL statement. If purpose of combobox is to offer list of equipment ID/Name, why are you including the QuestionID?

[JFo]

Here is the database, on main menu form click add questions button this is the form with the combo in question. QuestionID or ChecklistId is essential to create the questions or edit the questions for a specific piece of equipment

latest2.zip

Thanks

[June7]

As I said, QuestionID makes every record in the query unique. I think you should consider two comboboxes that cascade - one's RowSource is dependent on another's value. Check out tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section.

[JFo]

This selection of the combo box populates the subform containing the questions for each equipment type as can be seen in database. So it can't be done with one combo box and I have to use 2 you are saying? Thanks

[June7]

It could be done with one, just can't have the EquipmentID show as distinct with the ChecklistID field in the query. Make the ChecklistID column visible in the RowSource and users can see the uniqueness of each row in the list. Otherwise, go with the cascading comboboxes.

[JFo]

OK I will try with 2, including QuestionID is not an option as with up to 150-300 questions per piece of equipment this would not be appropriate

Thanks