Access 2016: Sorting Fields that Use Combo Boxes...

Question

I'm using Access 2016 to create a music library catalog.  The main table into which I'm entering data has a field for "Category", and when I set up the table I used a Combo Box for that field so there'd be a drop-down list of music styles to choose from.  I set up the Combo Box to take its options from a separate table, and that part works fine.

The issue I'm having is that, when I try to alphabetically sort the main table by the Category field, it won't sort.  I can sort any of the fields that are just basic text or number data types, but the combo boxes won't sort.  

I've read other MS Community posts about combo boxes, but most of them seem to be about alphabetizing the source data for the drop down list itself, and that's not what I'm trying to do.  I want to sort the entire table by the combo box categories.  

Any help would be appreciated!

Answer 1

That makes sense. Long Text datatypes are generally used for values of greater than 255 characters, like pages of stuff.

Answer 2

Thanks again.  I love the functionality of MS Access - it can do so much! - but it can also be a complicated program to master!

I changed my "long text" fields to "short text" fields and now I can sort them as well.

I'll keep working with this to develop my understanding... again, thanks for your help!!!

Answer 3

If a piece of music is identified by a category and a group then you should have foreign keys in the main table for those values. Once you do, its just a matter of joining the primary key in the lookup table with the foreign key in the main table.

Note, it is NOT necessary to create lookup fields on the table level. This is a bad practice in my opinion because it leads to the problem you encountered. What you should be doing is using comboboxes on the FORM level to do those lookups.

Answer 4

You can't join tables unless there are common fields between the two tables. What I instructed was to join JUST the main table with the Categories table. But you apparently joined other tables as well. Build your query one table at a time. And make sure there are common field

Answer 5

You created a lookup field in the main table for the Category. Lookup fields are not popular with professional developers because they mask what is actually stored in the table. So, in your case you are probably storing the primary key value of the Category, not its name. 

Bottom line that means you cannot sort the table directly by the Category name. But that's not really a big deal. You shouldn't be working directly with tables anyway. Instead, create a query that joins the main table with the Category table and add the Category name column from the Category table, instead of the ID column. Then you can sort that query on Category Name.