make listbox only show only ONE occurance of duplicate records 1

[david2001]

Hello

I have a problem with a listbox showing duplicate records when I want it to ignore them and just show ONE instance of the record.

Allow me to explain.

I have a listbox based on a query that displays a list of items dependent on what a user selects in a combo box on the same form.

For example:

In the combo box the user selects "Apples" and in the list box all the different type of apples is displayed like so:
fldFruit fldType
-------- -------
Apples Eating
Apples Desert
Apples Cooking

In the table that the query gets the details from, there are duplicate entrys as the table also lists the different suppliers of apples and you can get the same type of apple from different suppliers.

My problem is that the list box shows all the entrys in that table for the item selected in the combo box and I only want ONE instance of each apple type.

For example:
List box currently shows:
fldSupplier fldFruit fldType
----------- -------- -------
Hallam & son Apples Eating
Hallam & son Apples Desert
Gregsons Apples Cooking
Gregsons Apples Eating
Gregsons Apples Desert

What I want it to show is:
Hallam & son Apples Eating
Hallam & son Apples Desert
Gregsons Apples Cooking

The important field for me in the listbox is fldType in as much as, that is where the duplicate data is. I have tried using SELECT and SELECT DISTINCT and SELECT DISTINCTROW both in the query's SQL and the combo boxes SQL but with no luck.

Can someone kindly help me ou and tell me what I am doing wrong please.

Thank you in advance.

 

[david2001]

ANY suggestions Guys n Gals PLEASE!!!!!!

 

[tlbroadbent]

Do you need the supplier name in the list box? It doesn't make any sense to have the supplier name if you are keying off the furit and type. Remove supplier from then query and list box. Then use DISTINCT or GROUP BY to eliminate duplicates.

If you need to keep the supplier field in the query and list box for some reason, use one of the other aggregate functions, MIN, MAX, FIRST, LAST to select one supplier in the result set for every combination of fruit and type.

Select
Min(Supplier) As MinSupplier,
Fruit, Type
From table
Group By Fruit, Type Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[THoey]

I really think that you need to have multiple comboboxes and use them to narrow you selections down:

Combobox1 - Suppliers
Combobox2 - Fruit
Combobox3 - Type

In the LostFocus or OnChange events of the boxes, you could requery the next combobox to shorten the available selections. This way, you could use the DISTINCT clause to give you just the suppliers names, and then once a supplier is selected, just the fruit they sell, etc...
Terry M. Hoey

 

[david2001]

Hi Guys,

Just wanted to say a BIG thank you for all your help, it works great now thanks to your suggestions. It was actually Terrys suggestion that did it for me.

Thank you again.

David