Sorting

[neilwebber]

slightly oddball sort criteria

Hello again all

I have a table with a field 'SiteNumber' (a primary key). Values for SiteNumber take the form (for simplicity)

PLS00001A
PLS00001B
PLS00001X
PLS00002A
PLS00002B
PLS00002X

I'd like to sort this table so that the records appear as follows

PLS00001X
PLS00001A
PLS00001B
PLS00002X
PLS00002A
PLS00002B

ie for each number, move the 'X' record to the top, sort the rest alphabetically and then move to the next number, 'X' to the top and so on

Can anyone suggest anything to put in the the 'criteria' box in an advanced sort that might achieve this?

many thanks

Neil

 

[neileg]

I would add a caculated field that converts the data to a number that will sort the way you want. For example, translate the X to 1, A to 2, B to 3. Then do a straight sort.

 

[neilwebber]

Hi Neil

Thanks for a (very) quick response. What you suggest is actually my 'Plan B' - I thought of adding an incremental number field and sorting on that although your suggestion seems to allow less scope for error. However, I'm reluctant to have an extra field if I can help it and would hope to solve the problem through some kind of sort criteria if possible. Then again, maybe I'm just being fussy!

cheers
Neil

 

[Mile-O]

What you suggest is actually my 'Plan B'

Think about it - go with Plan B.
If Plan A worked you wouldn't have posted the question.

Anyway, the calculated field need not show in the end result.

 

[emalrola]

Having looked at your data, it strikes me that the codes ending in 'X' actually mean something else. In my experience, users tend to adapt codes to represent data if they can't get the functionality they want.

I would check with your users why exactly they want the X to come first - if, say , it represents a priority contract, then maybe you need a separate database field to actually hold this info. Maybe you actually have two fields - number and priority which have been concetenated into one for convenience,. It does not mean you have to store it like this.

If codes mean things, then generally your data structure is a little dubious....

But then again the user is alway right....

 

[neilwebber]

Thanks all for the further responses. I've thought about emalrola's suggestion but for some complicated reasons the data really does need to be stored in the specified format. So, Plan B swings into action and then comes to a halt as I try to work out how to carry it out.

I've added a text field (as the field to sort on) to the relevant form and this field is populated with the codes (PLS00001X etc) via a query.

I can also substitute the X for a 1, A for a 2 etc via a Replace and Case Select function

but I'm not sure how to fit it all together so that everything updates automatically (ie on addition of a new record etc). As always, it may be that I'm coming at it from the wrong angle entirely.

regards
Neil

 

[neileg]

Like I said, you need to use a calculated field in your query. You should always base your forms on queries, not on tables. This is a good example of why.