Software
I am making a list ncorporating letters and figures such as:
N1201P
N20EG
N249AU
N41135
N42NA
N6391C
N642XL
I need it to look like
N20EG
N42NA
N249AU
N642XL
N1201P
N6391C
N41135
Ant ideas how this can be done
Thank you
by mickparsons4428 Accepted solution
by tony hopkinson Accepted solution
Add another column of type integer. Number the items in the order you want them to come out and sort by that eg
Value SortOrder
N1201P 5
N20EG 1
N249AU 3
N41135 7
N42NA 2
N6391C 6
N642XL 4
Select Value From MyData order by sortorder
you might want to do sortorder in tens or some such to give you and easy way to insert new fellows in with out having to renumber those that come after.
by mickparsons4428 Accepted solution
Its actually the United States Civil Aircraft Register and could be over 500,000 entries
Thanks amyway
by tony hopkinson Accepted solution
Date registered?
Short of the admittedly for 500,000 records unacceptable option of imposing a sort order you need to know it.
Then do you have that data, otherwise give up all hope of getting them out in the same order.
by tony hopkinson Accepted solution
The reserve N number one perhaps?
If so you can get sneaky.
Create a table to import into add an autonumber type key. This key will end up being in the same order as the input N Number presumably. You'll need to reset the start value of teh autonumber each import but
Truncate Table MyFAAImport, should do that when it empties out the previous download.
May be this will give you an idea anyway.
by fwang Accepted solution
Assume the table name is temp, the field name is ID, build a query:
SELECT temp.ID
FROM temp
ORDER BY Val(Mid([ID],2));
You will get the output you want.
by tony hopkinson Accepted solution
but the example list does go up by the numeric part.
would n't deal with
N4221F
N4221D
if D should be before F
You could probably get somewhere chopping it up though be damn expensive with 500,000 entries though.
ORDER BY Val(Mid([ID],2,Datalength([ID]))), ID)
might do the trick if it is the order though.
If it does work I'd be tempted to stick the val(mid.... result in an extra column and index it.
This discussion is closed to new replies.
Sort by Trending