Preferred way to do sort order - column in table, or specified in query?

[Rachel Garrett]

Greetings,

I have a category in my database with four possible values. Call them
North, South, East, and West. When I run queries and reports, I always
want them to come back as North, South, East, and West -- not
alphabetically as East, North, South, and West.

What is the preferred way to to do this? I could add a column to my
Category table:

Name Order
----------------------
North 1
South 2
East 3
West 4

Then, in all queries that include Category, I could pull back the
Order column as well, and sort by Order ascending.

What I was hoping is that there was some way, in the design view of
the query, to specify the sort order there. E.g., "Sort by [Category]
["North","South","East","West"] or similar.

Any ideas?

Thanks,
Rachel

 

[Rich/rerat]

Rachel Garrett,
Instead adding a column, you could just rename your values, such as:
North to 01 North
South to 02 South
East to 03 East
West to 04 West

--
Rich/rerat
(RRR News) (message rule)
((Previous Text Snipped to Save Bandwidth When Appropriate))


Greetings,

I have a category in my database with four possible values. Call them
North, South, East, and West. When I run queries and reports, I always
want them to come back as North, South, East, and West -- not
alphabetically as East, North, South, and West.

What is the preferred way to to do this? I could add a column to my
Category table:

Name Order
----------------------
North 1
South 2
East 3
West 4

Then, in all queries that include Category, I could pull back the
Order column as well, and sort by Order ascending.

What I was hoping is that there was some way, in the design view of
the query, to specify the sort order there. E.g., "Sort by [Category]
["North","South","East","West"] or similar.

Any ideas?

Thanks,
Rachel

 

[John W. Vinson]

Greetings,

I have a category in my database with four possible values. Call them
North, South, East, and West. When I run queries and reports, I always
want them to come back as North, South, East, and West -- not
alphabetically as East, North, South, and West.

What is the preferred way to to do this? I could add a column to my
Category table:

Name Order
----------------------
North 1
South 2
East 3
West 4

Then, in all queries that include Category, I could pull back the
Order column as well, and sort by Order ascending.

What I was hoping is that there was some way, in the design view of
the query, to specify the sort order there. E.g., "Sort by [Category]
["North","South","East","West"] or similar.

Any ideas?

Thanks,
Rachel

The Order field should be renamed - Order By is a valid SQL keyword, and
Access is going to get confused if you say Order By Order - but if you change
the name of the field to Sortkey or SortOrder or the like, then having that
field is indeed the best way to accomplish what you're asking.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Rachel Garrett]

The Order field should be renamed - Order By is a valid SQL keyword, and
Access is going to get confused if you say Order By Order - but if you change
the name of the field to Sortkey or SortOrder or the like, then having that
field is indeed the best way to accomplish what you're asking.

Thanks! It makes me feel better to know this is the "right way to do
it," not a hackaround.

--Rachel