Query Problem

[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

[Ray at]

What happens when you try to ORDER BY that column?

Ray at work

"Stuart" <st****@nowhere.co.uk> wrote in message
news:u4**************@TK2MSFTNGP10.phx.gbl...

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

[John Blessing]

"Stuart" <st****@nowhere.co.uk> wrote in message
news:u4**************@TK2MSFTNGP10.phx.gbl...

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

[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

"Stuart" <st****@nowhere.co.uk> wrote in message
news:u4**************@TK2MSFTNGP10.phx.gbl...

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

[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

"Stuart" <st****@nowhere.co.uk> wrote in message
news:u4**************@TK2MSFTNGP10.phx.gbl...

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