Sort by range of rows?

[squirrly]

Not sure if this is the right forum to be asking this, but can a range of rows be sorted in Access? I may have a design problem as well...

I have one table listing all breeds by type. My queries work fine pulling all breeds into a dropdown list according to type.

BREEDS
BreedID (FK)
Breedname
TypeID (FK) (cats, dogs, birds)


At the beginning of each new list, I have "other" and "unspecified". For example:

Breedname TypeID
Other 1
Unspecified 1
Abbysinian 1
Bengal 1


Cornish Rex 1
Other 2
Unspecified 2
Airedale 2
Beagle 2
Collie 2
Other 3
Unspecified 3
Amazon 3
Canary 3
Dove 3

The breednames were initially entered alphabetically, but I will sometimes need to ADD a breed as well at the bottom of the list. How can each range of breednames by type be sorted alphabetically while keeping "Other" and "Unspecified" at the top of each list? I can throw an underscore at the beginning of each, but that doesn't look good as output. I'm thinking this is more of a SQL issue, but thought I'd try here first.

As I ask this, I realize that multiple "Other" and "Unspecified" is probably bad...

Thanks for your help!

[pbaldy]

As I ask this, I realize that multiple "Other" and "Unspecified" is probably bad...

That was my first thought, but you could achieve the result with another field, like "SortOrder". Make it the primary sort field, and put 0 in for "Other" and "Unspecified" and 1 for everything else.

[squirrly]

Well, now, THAT is an interesting thought and very, very simple!!!

Just tried it and it works like a charm as long as I give the SQL the first/second fields to sort. Awesome, THANK YOU!
Working completely alone can run a person insane, so I'm thankful for this forum! Should've asked weeks ago....

[pbaldy]

No problem, and glad you found us!