Sort mixed numbering system

Question

Hello.

I was wondering if anybody had some ideas on this problem I'm having.

Consider a Field in a Table has entries of the form L.L.L.L where the L's are MS Word numbering levels. For example 1.i.a.iix.B.

The number of levels in each record varies eg the entry might be 1, or 1.i or 1.i.a etc.The maximum number of levels is not limited, but in my particular data set, 7 levels is the most.

The numbering type at each level can be:

•  Basic alpha a-z then aa - az, then ba-bz etc - I have assumed that this sequence would never reach "ic" which would be the first clash for roman numerals aside from the problem ones mentioned below.
  
•  Roman numerals
•  Number list

I'm trying to sort on the fields but this doesn't work because at any level:

  "i" could mean the 1st in the sequence (roman) or the 9th (basic alpha), and

  "v" could mean the 5th in the sequence (roman) or the 22nd (basic alpha), and

  "x" could mean the 10th in the sequence (roman) or the 24th (basic alpha)

My plan of attack so far has been to split the field entry into its levels, then with VBA functions in calculate fields decode each level into a numeric, then put it all back together like nn.nn.nn then sort on that. So a.iv.3.b would become 01.04.03.02 etc.

All that works well except for the problem mentioned above. I think the only way to resolve the issue is :

• In the case of getting an "L.L.L.i" I need to check in the same recordset  if "L.L.L.h" exists, if so  the "i" was a letter, otherwise the "i" was a roman numeral.
• In the case of getting an "L.L.L.v" I need to check in the same recordset  if "L.L.L.u" exists, if so  the "v" was a letter, otherwise the "v" was a roman numeral.
• In the case of getting an "L.L.L.x" I need to check in the same recordset  if "L.L.L.w" exists, if so  the "x" was a letter, otherwise the "x" was a roman numeral.

So all this is starting to get quite messy, the above implies I need to do like a conditional subquery test or something .

I was wondering if anybody had encountered this before and had an elegant solution. I've googled but not found anything.

Answer 1

I was wondering if anybody had encountered this before and had an elegant solution. I've googled but not found anything.

Hi Kelvin,

If the first entry of a new level starts with "i", you can decide to follow the Roman sequence.

Another solution could be to add a "sort" column in your table. It is not unusual to do that when you want display text fields in a non-alfabetic order.

It introduces some redundancy. On the other hand the translation needs only be done one time when storing the data, instead of every time you want the data sorted using a query. Just a matter of efficiency. This translation on data entry can be automated with the same routine you have now, but there is the possibility to correct it when the translation was not what you wanted.

Imb.

Answer 2

Another solution could be to add a "sort" column in your table. It is not unusual to do that when you want display text fields in a non-alfabetic order.

It introduces some redundancy. On the other hand the translation needs only be done one time when storing the data, instead of every time you want the data sorted using a query. Just a matter of efficiency. This translation on data entry can be automated with the same routine you have now, but there is the possibility to correct it when the translation was not what you wanted.

Imb.

Thanks Andre. Im trying to avoid having to manually retouch the data. The list is long and I get regular updates so I would be constantly updating the manual sort field.

I think a fully automated solution should be possible but it will require some type of subquery which only conditional runs if it the "i", 'V' or "x" scenario. I just dont know how to set that up in the most efficient manner.

Answer 3

I think a fully automated solution should be possible but it will require some type of subquery which only conditional runs if it the "i", 'V' or "x" scenario. I just dont know how to set that up in the most efficient manner.

Hi Kelvin,

In that case you could use the logic that when a new sublevel start with an "i" you have to take the Roman numbering (as compared to "1" for numeric, or "a" or "A"form alphabetic).

Imb.