Forum Discussion

AmedMesa's avatar
AmedMesa
Copper Contributor
Oct 23, 2019

Combo box with "All" added to query and sorted on top.

I have a category list that will be added to a combo box.  Here is my SQL....   SELECT tblCategoryList.Category FROM tblCategoryList UNION SELECT "All" FROM tblCategoryList ORDER BY tblCategor...
access
  • AmedMesa's avatar
    AmedMesa
    Oct 25, 2019

    Thanks guys for helping me solve my issue. Here is my working code:

    ' SELECT tblCategoryList.Category As Category, "1" As ID
    ' FROM tblCategoryList
    ' UNION
    ' SELECT "All Tags", 0
    ' FROM tblCategoryList
    ' ORDER BY ID, Category;

    Basically I created a row in SQL called ID and all records added from the tblCategoryList table are labeled 1 under ID. The Union adds the "All Tags" and under the ID column the row is identified with 0. Then I sort via ID and followed by Category. Now I can keep the order ascending and no matter what the "All Tags" remains on top.

testmuts's avatar
testmuts
Brass Contributor
Oct 24, 2019

AmedMesa 

 

I would make an extrta column in the query and skip it in the combobox. 

 

SELECT tblCategoryList.id, tblCategoryList.Category

FROM tblCategoryList

UNION

SELECT 0, "All"

FROM tblCategoryList
ORDER BY tblCategoryList.id, tblCategoryList.Category;

 

Greetings,

 

Marcel

  • testmuts's avatar
    testmuts
    Brass Contributor
    Oct 24, 2019

    testmuts 

     

    Sorry I posted wrong SQL before. Needs to sort better :)) 

     

    SELECT iif(tblCategoryList.id=0,0,"1"& tblCategoryList.Category) as zCategory, tblCategoryList.Category

    FROM tblCategoryList

    UNION SELECT 0,"all"

    FROM tblCategoryList
    ORDER BY zcategory

     

    With this you can skip the first column and it will work as needed. 

    • AmedMesa's avatar
      AmedMesa
      Copper Contributor
      Oct 25, 2019

      Thanks guys for helping me solve my issue. Here is my working code:

      ' SELECT tblCategoryList.Category As Category, "1" As ID
      ' FROM tblCategoryList
      ' UNION
      ' SELECT "All Tags", 0
      ' FROM tblCategoryList
      ' ORDER BY ID, Category;

      Basically I created a row in SQL called ID and all records added from the tblCategoryList table are labeled 1 under ID. The Union adds the "All Tags" and under the ID column the row is identified with 0. Then I sort via ID and followed by Category. Now I can keep the order ascending and no matter what the "All Tags" remains on top.

Resources