Select Distinct

I need to select the distinct Category values that appear in a table to use them to drive a loop processing the table contents. I need the categories to be sorted by first the Order field, and then the Category field. However, I can't figure out how to do this. If I pull the distinct values from the table itself:
sqlstr2 = "SELECT DISTINCT FAQsKB.category FROM FAQsKB WHERE FAQsKB." & group & " = True"
Set cats = dbs.OpenRecordset(sqlstr2, dbOpenDynaset)
I get each category listed once. However, they appear in alphabetical order of category.
If I pull the distinct values from a query that sorts the data the way I want it to be sorted, each category appears multiple times.
I've also tried sorting the table on the appropriate fields, but this has no effect on the output.
It's not possible to add an "Order by" statement to the Select distinct statement, unless it's to order by the category field. Which doesn't help. )
Anyone have an idea how I can get a list of just the distinct categories yet force them to be sorted as I want?
Thanks!

Hi,


Try this:


Michiel

If I use the code exactly as typed, it results in a syntax error in the Order By clause.

If I modify the code to end ORDER BY FAQsKB.order, FAQsKB.category it results in an error stating that the Order By Order conflicts with distinct.

you must add the FAQsKB.order fied in your select part, to use it in the order statement



hope this helps

Yes! That seems to solve the problem! (Though I have to upload the XML to be sure it's working right...)

Thanks!

no problem