Sorting?

  • Thread starter Thread starter subscription_junkie
  • Start date Start date
S

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.
 
subscription_junkie said:
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.
Click to expand...

ORDER BY Right(Format([EmployeeID, "00"), 2)
 
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)
 
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
 
Back
Top