make listbox only show only ONE occurance of duplicate records 4

[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.

 

[JAXDBase]

select distinctrow fldSUPPLIER as row source

then resize /reconstruct your listbox to 3 columns. it should now do what you want. It will distinct the fldSUPPLIER only and pick the first occurence of all the other columns.

if you have the know how to do VBA, create a recordset using the above, then use a loop to listboxNAME.additems, etc. more work here but more satifsying..

 

[DougP]

did you try "Group By"

open the query grid and click the "E" epsilon button its near the Run (red Exclamtion mark) button.

it will add a group by for you

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[CosmoKramer]

Couldn't you add another combo box for fldSupplier, then your criteria for your list box could be Select Distinct fldSupplier, fldFruit..... and that would return distinct types...

Why would you want to "randomly" return suppliers??

 

[david2001]

Hi guys,

Thank you all for your suggestions but this just went from bad to worse.

I tried Dougs suggestion and nothing happened so I tried erogla's suggestion and still got the multiple records so I changed everything back VERY carefully and now when i try to open the form access gives the following error message:
"syntax error in FROM clause".

I have checked and double checked then re-checked every FROM clause I can find and everything looks just fine. I simply am at a loos as to why it is doing it and my PC has come very close to being thrown out of the window.

Is there any way of trapping or finding out the exact line or FROM clause thats is causing this (simular to the debug window) ?????

I feel I am just making a mess of this whole problem so I am at the mercy of your kindness.

 

[JimHorton]

David,
The Group by will do if you want the 3 fields, but ONLY group the fields you want unique--the fldFruit and fldType:
Select first( fldSupplier),First(fldFruit),fldType from [whateverTableName] Group by fldType,fldFruit Having fldFruit = '" & Me!YourFldTypeComboBoxValue & "'"

Distinct will work if you only want the show the fldType and fldFruit--the main difference here with regard to group by is that with group by you can show other fields, but those other fields will only be an aggregate result, ie First, Sum, etc.

But, since you wanted all 3 fields, you will get the random first supplier, which won't mean alot if all you *really* want is the fruit and Type in the listbox.

--Jim

 

[JimHorton]

David,
Big oops....
In my last post, remove the First from the fldFruit...Sorry about that...
--Jim

 

[david2001]

Hi Guys,

Just wanted to say a BIG thank you for all your help, it works great now thanks to your suggestions.

Thank you again.

David