Combo Box problem

[liltechy]

I have a user that created a table (does not want it change in any way) in a Training database. She created a form based on the training requests (tblTrainingReqs) and wants to create a combo box to find the trainers and their requests. I have created the combo box and each trainer is listed various amounts of times. I selected DISTINCT in the rowsource property and I still have duplicate names. How do I show in the drop down list each trainer one time?The combo box is based on the Trainer field. PLEASE HELP


liltechy

 

[PauloRico]

Create a Group By query. This will only select each individual value once.

 

[scriverb]

This SQL should do it. This is utilizing the Group By clause in SQL which PauloRico suggested.

Select A.Trainer
FROM tblTrainingReqs as A
Group By A.Trainer
Order by A.Trainer;

Place this in the Rowsource of the Combobox.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[cghoga]

are you only displaying trainer in the combo box or both trainer and request?

if you only want to show the trainers in the drop down distinct would work.

if both, you will see trainer listed multiple times depending on the amount of requests the trainer has.

if you only want to see the trainer name once, but see all the requests for that trainer, you may look at putting the trainer on a "main" form and listing the requests in a subform.

or you may be able to create a combobox for the requests by adding a text field for trainer name, and autofilling the combobox by basing the query within the control source of the combo box on the value of the text field.

i hope this helps.