Query Problem

S

Stuart

This might be the wrong group (but it is my favorite)

I need to sort a list data from a text field into numerical order.

The data needs looks like this: -

a1
1
1a
2
3
4
29
29A
29X
25X
X25

However, the data could be put into the database in any order with additions
by a user so I cannot rely on using an autoID.

I am using MS Access (I could use MySQL if it would make life easier, but
not MS SQL Server as we do not have a licence for it)

Any help would be greatly appreciated !!!

regards,

Stuart
 
J

John Blessing

Stuart said:
This might be the wrong group (but it is my favorite)

I need to sort a list data from a text field into numerical order.

The data needs looks like this: -

a1
1
1a
2
3
4
29
29A
29X
25X
X25

However, the data could be put into the database in any order with additions
by a user so I cannot rely on using an autoID.

I am using MS Access (I could use MySQL if it would make life easier, but
not MS SQL Server as we do not have a licence for it)

Any help would be greatly appreciated !!!

regards,

Stuart

Data in a referential database has no intrinsic order, it is determined by
the "order by" clause in your sql statement. E.g.:


"Select column1, colum2 from table1 ORDER BY column3"


--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
 
S

Stuart

Ray,

the query and results are as follows

SELECT tblTimetables.TT_BOOKA, tblTimetables.TT_id,
tblTimetables.TT_WWWNumber
FROM tblTimetables
WHERE (((tblTimetables.TT_BOOKA)=True))
ORDER BY tblTimetables.TT_id, tblTimetables.TT_WWWNumber;


TT_WWWNumber
1
5
9
10
12
14
19
22
29
63
64
65
99
99
152
161
163
01
04
29A
5B
AL
CC
KC
SV
SV
SV
SV

As you can see from the list 01,04,29A,5B are out of order.

I was wondering if there is any jiggery pokery that can be done either in
the SQL statement or in ASP some how.

regards,

stuart
 
S

Stuart

I have found a quick fix

Next to the route number I have assinged an integer that is the same as the
numeric route number and for Alpha route numbers I have assigned a value of
1000.

I sort on the new integer column and then sort by the routenumber.

This produces an acceptable result. Probably not the best solution, but it
works!!

Stuart
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top