asked on

Query Sort with Alpha and Numbers

This query is driving me crazy! The screenshot shows the result. The primary sort is the SortOrder of the Group, which is find, but the secondary sort is the Animal Number.

See how 20-100 is before 20-96. This happens for all sorts of our data.

How can I get it to sort correctly? Keep in mind sometimes Animal Numbers could be A1, A2, B3a, B4b, etc.

I need the sort to be able to totally figure out the correct order, regardless.

Here is the SQL statement. Screenshot 2 is the query window.

SELECT dbo_Animals.StudyID, dbo_TissueListGroups.StudyPhase, dbo_Animals.Received, dbo_Animals.NoHisto, dbo_Animals.GroupID, dbo_Animals.AnimalID, dbo_AnimalsGroupNames.SortOrder, dbo_Animals.Gender, dbo_Animals.GroupNum, dbo_Animals.AnimalNumber, dbo_Animals.AnimalNumberShort
FROM (dbo_Animals INNER JOIN dbo_TissueListGroups ON dbo_Animals.GroupID = dbo_TissueListGroups.GroupID) INNER JOIN dbo_AnimalsGroupNames ON (dbo_Animals.GroupNum = dbo_AnimalsGroupNames.GroupNumberName) AND (dbo_Animals.StudyID = dbo_AnimalsGroupNames.StudyID)
WHERE (((dbo_Animals.StudyID)=[StudyID:]) AND ((dbo_Animals.Received)>[Received:]) AND ((dbo_Animals.NoHisto)=0) AND ((dbo_Animals.GroupID)=[Grouping1:] Or (dbo_Animals.GroupID)=[Grouping2:] Or (dbo_Animals.GroupID)=[Grouping3:] Or (dbo_Animals.GroupID)=[Grouping4:]))
ORDER BY dbo_AnimalsGroupNames.SortOrder, dbo_Animals.Gender DESC , dbo_Animals.AnimalNumber;
query-problem.gif
query-design-window.gif

Rey Obrero (Capricorn1)

create another column with this

clng(replace([animalnumber],"-",""))

and use this for sorting

mbizup

Create a separate column for sorting, padding the two portions of the string with zeros:

SortColumn: format(left(AnimalNumber, instr(1,AnimalNumber,"-")-1), "000") & "-" & format(mid(AnimalNumber, instr(1,AnimalNumber,"-")+1), "000")

Open in new window

Then sort on that column.

I'm using three zero's but use enough to cover however many digits you need in each part of the number.

The result will be for example

020-090
020-100

etc...

Which should sort okay regardless of the length of either part of the string.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharpapproach

ASKER

This worked Perfectly!!!! All the other answers did not work for all Alpha and Numeric Combo.,..

Thanks sdstuber!