470,647 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

Query Problem

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
Jul 19 '05 #1
4 1312
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

Jul 19 '05 #2
"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
Jul 19 '05 #3
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

Jul 19 '05 #4
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

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Adam Louis | last post: by
6 posts views Thread by Martin Lacoste | last post: by
11 posts views Thread by Andy_Khosravi | last post: by
4 posts views Thread by Stan | last post: by
2 posts views Thread by existential.philosophy | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.