Sort on two fields?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a home inventory database. The first two fields on the form are
Category (drop box of 3) and Item name (text field).

I would like to sort my records in this order:
Category: Misc., Comp. hardware, comp. software
Then, within each category:
Item Name: Ascending

In other words, I'd like to manually sort the category (since it's not
alphabetical above), and within the categories, sort the items
alphabetically. How do I set this up, and make it permanent?
 
Presumably you have a table of the category values? Add another field:
SortOrder Number (integer) lower numbers sort first.

Now create a query that contains both tables, and sort by this number +
[Item Name]. Use that as the source for your form.

That should work fine provided you are not actually trying to modify the
Category fields in your form, all records actually have a category, and
there are no field in the Category table that have a Default Value assigned.
 
Hmmm...not sure I understand. Upon form data entry, the item categories are
presented from a combo box. I need to sort by category, then within the
categories, sort by item name.

Allen Browne said:
Presumably you have a table of the category values? Add another field:
SortOrder Number (integer) lower numbers sort first.

Now create a query that contains both tables, and sort by this number +
[Item Name]. Use that as the source for your form.

That should work fine provided you are not actually trying to modify the
Category fields in your form, all records actually have a category, and
there are no field in the Category table that have a Default Value
assigned.
Click to expand...
 
Hmmm...not sure I understand. Upon form data entry, the item categories are
presented from a combo box. I need to sort by category, then within the
categories, sort by item name.
Click to expand...

Tables don't contain combo boxes. They contain data. A Form or a Combo
Box is simply a tool - you can't sort by a combo box!

What is the structure of your *table*? Is the Category stored as text,
or as a foreign key to a categories table, or as a <yuck, PTOOIE!>
Lookup field (which conceals a numeric foreign key behind the lookup)?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Well, the Category is in a field by itself, if that's what you mean. In its
Properties, the Row Source Type is "Value List"...
 
Well, the Category is in a field by itself, if that's what you mean. In its
Properties, the Row Source Type is "Value List"...
Click to expand...

You are another victim of Microsoft's misdesigned, misleading,
abominable Lookup Wizard misfeature.

The Category *DOES NOT EXIST* in your table. What exists in your table
is a numeric Autonumber value, concealed from your view by the Combo
Box. When you sort by Category, you're sorting - not by the visible
category text - but by what is actually stored in the table, concealed
from your view.

Try changing the Lookup Type from Combo Box to Textbox to see what is
actually there.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top