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]

Re: Query Problem

What happens when you try to ORDER BY that column?

Ray at work

"Stuart" <stuart@nowhere .co.uk> wrote in message
news:u4ZusCknDH A.2160@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
additions[color=blue]
> 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
>
>[/color]

[John Blessing]

Re: Query Problem

"Stuart" <stuart@nowhere .co.uk> wrote in message
news:u4ZusCknDH A.2160@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
additions[color=blue]
> 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
>
>[/color]

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]

Re: Query Problem

Ray,

the query and results are as follows

SELECT tblTimetables.T T_BOOKA, tblTimetables.T T_id,
tblTimetables.T T_WWWNumber
FROM tblTimetables
WHERE (((tblTimetable s.TT_BOOKA)=Tru e))
ORDER BY tblTimetables.T T_id, tblTimetables.T T_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" <stuart@nowhere .co.uk> wrote in message
news:u4ZusCknDH A.2160@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
additions[color=blue]
> 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
>
>[/color]

[Stuart]

Re: Query Problem

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" <stuart@nowhere .co.uk> wrote in message
news:u4ZusCknDH A.2160@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
additions[color=blue]
> 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
>
>[/color]