Formatting numbers

[Guest]

I have a field which refers to floors in the building. I would like them to
be formatted as numbers so that they sort numerically. However, several of
the floors below grade have letters rather than numbers, i.e., LL (for lower
level), LL1, LL2, etc. If I format for numbers, I can't use that
nomenclature. If I format for text, it doesn't sort numerically.

I considered using negative numbers rather than the ones above (LL1, LL2,
etc.), but that designation is used so frequently around here I'm afraid it
would be confusing. Any help or thoughts are appreciated. Thanks!

 

[Brian]

I have a field which refers to floors in the building. I would like them to
be formatted as numbers so that they sort numerically. However, several of
the floors below grade have letters rather than numbers, i.e., LL (for lower
level), LL1, LL2, etc. If I format for numbers, I can't use that
nomenclature. If I format for text, it doesn't sort numerically.

I considered using negative numbers rather than the ones above (LL1, LL2,
etc.), but that designation is used so frequently around here I'm afraid it
would be confusing. Any help or thoughts are appreciated. Thanks!

Create a lookup table called, say, "floors", with two fields, "floor_name"
and "floor_sequence". It should contain data such as this:

floor_name: floor_sequence:
LL2 1
LL1 2
LL 3
0 4
1 5
2 6

Then, when you need to sort by floor, you can join your floor_name field to
to the floors table and sort by the floor sequence. For example, if your
floor_name field is in a table called "employees", then the following query
returns employee_names sorted by floor_sequence:

SELECT employee_name, E.floor_name FROM employees E INNER JOIN floors F
ON E.floor_name = F.floor_name ORDER BY floor_sequence