Query Problem

Discussion in 'ASP General' started by Stuart, Oct 29, 2003.

  1. Stuart

    Stuart Guest

    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, Oct 29, 2003
    #1
    1. Advertising

  2. Stuart

    Ray at Guest

    What happens when you try to ORDER BY that column?

    Ray at work

    "Stuart" <> wrote in message
    news:...
    > 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, Oct 29, 2003
    #2
    1. Advertising

  3. "Stuart" <> wrote in message
    news:...
    > 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
     
    John Blessing, Oct 29, 2003
    #3
  4. Stuart

    Stuart Guest

    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" <> wrote in message
    news:...
    > 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, Oct 30, 2003
    #4
  5. Stuart

    Stuart Guest

    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" <> wrote in message
    news:...
    > 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, Oct 30, 2003
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    1,027
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,550
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    833
    David Gordon
    May 18, 2005
  4. Eric Nelson
    Replies:
    5
    Views:
    1,579
    Alexey Smirnov
    Feb 4, 2009
  5. Jon F.

    CAML Query: Multiple Query Fields Issue

    Jon F., May 12, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    774
    Jon F.
    May 12, 2004
Loading...

Share This Page