Sorting?

[subscription_junkie]

I am working with a field that has four numbers. This is not an autonumber
field, but stores the employeeID in a long integer format.

I am needing the report to sort by THE LAST TWO NUMBERS of the Employee ID.
So, if I have 0041, 8576, and 4529, I would need them to sort like:

4529
0041
8576

I've searched the 'net and through my Access Bible and have not found a way
to do this. If anyone can provide any assitance, it would be greatly
appreciated.

 

[Dirk Goldgar]

I am working with a field that has four numbers. This is not an
autonumber field, but stores the employeeID in a long integer format.

I am needing the report to sort by THE LAST TWO NUMBERS of the
Employee ID. So, if I have 0041, 8576, and 4529, I would need them to
sort like:

4529
0041
8576

I've searched the 'net and through my Access Bible and have not found
a way to do this. If anyone can provide any assitance, it would be
greatly appreciated.

ORDER BY Right(Format([EmployeeID, "00"), 2)

 

[Van T. Dinh]

You can create a Calculated Field in the Query:

SortNo: [EmployeeID] MOD 100

(which gives you the numerical value of the last 2 digits)
and apply the sorting on this Calculated Field.

However, the needs to do this indicates that your Table
Structure may not have been designed properly. The Field
[EmployeeID] seems to have 2 or more distinct parts with
each part conveys some different meaning. In effect, the
Field seems to store 2 or more items of data in each Field
value. This violates the First Normal Form of the
Database Noralization which requires each Field value is
atomic, i.e. each Field value stores a *single* item of
data, not a combination of items or a list.

Perhaps, you should check out the Relational Database
Design Theory and the Database Normalization and check the
structure of your database against these.

HTH
Van T. Dinh
MVP (Access)

 

[Luiz Cláudio]

Hi,

you can use Right$() function to retrieve the last two digits, and sort your
query using the result.

Luiz Cláudio C. V. Rocha
São Paulo - Brazil