Solved Difficulty sorting an alphanumerical text field (1 Viewer)

[Ken Sheridan]

I have resolved this issue by adding a sort field. This field contains a higher number (5) where there are no leading zeros, a smaller number (4) where there is one leading zero, a smaller number (3) if there are two leading zeros etc.

There is no need for that. Arnelgp's solution caters for any number of leading zeros. More importantly, adding the extra column means that the table is no longer normalized to Boyce Codd Normal Form (BCNF) as the new column is functionally determined by the registration number, which is not a candidate key. The formal definition of BCNF is:

A relvar is in BCNF if and only if every nontrivial, left-irreducible functional dependency has a candidate key as its determinant.

Informally this requires that every non-key column's only determinants must be candidate keys. The lack of normalization to BCNF leaves the table open to the risk of update anomalies.

 

[The_Doc_Man]

The solution of adding a column for a "sorting number" is still technically incorrect. (See Ken's comments, which ARE technically correct.) Add that column, but rather than make it an arbitrary sorting number, make it the Register number. Then you can use the entry numbers in each register as-is. If you make the register number equal to the year of the first entry in that particular register, it doesn't even matter the order in which you enter the records.

Once you take the step of adding that extra column, make it a technically correct column. That would make the DB entry have a compound primary key equal to [RegisterYear], [EntryNumber] ... which Access handles perfectly well.