I’ve created a list box on a form that I can use to select what room an item is currently assigned to. Currently it doesn’t matter how the referenced table is sorted, the items in the list box always appear in alphabetical order. Is there a way to specify a different sorting so that I can put the most used options at the top?
Default query for a listbox will be
SELECT "Name", "ID" FROM "Table" ORDER BY "Name"
Name will be shown, ID will be written to the data source of the form. Name will be listed alphabetical sorted.
SELECT "Name", "ID" FROM "Table" ORDER BY "ID"
Now sorting will be set by field ID. If you have grouped the Names this way ID will work for you. But might be you need a special field in “Table”. I will call it “Sort”:
SELECT "Name", "ID" FROM "Table" ORDER BY "Sort"
So no problem to get any sorting of a field in “Table” you want.
Sometimes it helps if a new user, like me, answers too. You can choose to show values in a list box directly from a table, or indirectly from a query based on that table. (there are perhaps more complicated ways). So it is very common to create a query, arrange for it to provide results sorted in the way you want, then use that query as the source for the list box.
I attached a demonstration database. There are 2 forms. Items uses a typical query with an alphabetized list of choices. Items2, the query lists the rooms in the order of most used. Change a few of the room selections on the table then press the Refresh button below and the list will probably be in a different order.
Query5 is used to supply data for the ListBox on Items2. You can see it is a bit more complex that the typical query. It groups the Items by the Rooms ID and counts how many Items are in each room. The next level of the query adds the name of each room. At that point it would work fine but only the rooms with items in them would be on the list. The three rooms F, G and H wouldn’t appear on the list because they have no items assigned to them. If you started with an empty table there would be no rooms on the list!
To resolve that dilemma requires a second query that lists all rooms that don’t appear on the first list. A UNION statement joins the 2 lists together.
Finally, the list is sorted highest count first and where the counts are equal they are in alphabetical order.
Query5C is much the same as Query5, but it has comments and for testing purposes it displays the count for each room.
Something to consider. That dynamic list may end up being more confusing than helpful.
Demo67.odb (22.5 KB)