Query sort by providing a list

[Mr930]

I have a database table where the records are people. On each record I have a region listed. What I want to do is somehow tag the records based on a sort of the regions, but the sort is not alphabetical. Outside Access I calculate a list of the regions, and they can end up in any order. Is there a way to have Access sort based on a list I provide?

thanks
Fred E.

 

[Micron]

What I want to do is somehow tag the records based on a sort of the regions,

No idea what that means, or how you "calculate" a list of regions. You are doing this sort in a query? Then apply a sort order to the field or fields you need. If you're doing this in a table, the short answer is, don't. Your users shouldn't be dabbling in tables anyway. In fact, they should not even be able to see them.

 

[Mr930]

The goal is to create a query where the records are sorted by region. But I have a list of regions in an order that is not alphabetical. I want to be able to pass my list (e.g. B,A,D,G,E rather than A,B,D,E,G) and have the records sorted in that order. I am wondering if Access has this capability.

 

[Micron]

You can sort table records in a query in either descending or ascending order. The table order does not matter. Choose the sort option in the query design grid.

 

[Mr930]

I know that method but that is alphabetical. Not what I need.

 

[Micron]

Sorry, I didn't read your post closely enough.
Then you need a sort order field. The only thing you can sort on is data, and if you want a self determined order, you'll have to use something that provides that. If necessary, you'll have to add a field and enter values in the records according to the sort order you need. In those cases, I increment by 5 or 10, not 1 because it's inevitable that sooner or later, something would need to come between 1 and 2.

 

[JonXL]

Create a separate table with your regions and their sort orders. Then join your main data table to the sort table on their region fields and ORDER BY the sort field. As Micron suggests, increment your sort values by 5 or 10.