Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Ordering Values In Proper Sequence In A Query

    Hi Guys,

    I have a table which contains data from my iTunes XML file. I'm trying to display my data by Artist, Album, Disk Number, and Track Number so that I can more easily verify that there is no missing track information. Both the Disk Number and Track Number fields are text fields of two positions in length. Right now the Track Numbers are out of sequence and are being displayed in sequences such as 1,10,2,3,4. I would like to display them in their proper order but cant seem to get the query to cooperate. I've tried using CInt to convert them to numeric values but that didn't help. I know this should be simple, but I can't figure out what I'm missing. Could somebody please give me a slap in the head and help me out?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the data is numeric, then use a numeric datatypefor the column. Or if you must persist in using a string/text datatype pad with leading spaces or zeros.
    Of tbe two solutions the 'right' way is to fix the datatype, padding is a kludge workaround

    To pad run an update query such as
    Code:
    update mytable set trackno = " " & trackno where len(trackno) = 1
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Thank you for your assistance. I thought of the padding solution shortly after posting this. In reference to my using text fields. Firstly, that's how the data imported to Access. Secondly, I agree with this because my DMBS instructor always told us that if you did not need to use the field for calculations then it should be defined as text even if the value is numeric. I can't see how I would need to do calcs using the track number or disk number so they are defined as text as is nearly every other field in my DB.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's not always about calculations as you have just found - ordering is important to!

    This field should definitely be an integer, not a text field.

    Phone numbers and zipcodes are the most common examples of when what looks like a number needs to be stored as text. That's because numbers don't have leading zeroes, but phone numbers do. Phone numbers can also include symbols (e.g. +) and extensions. Zipcodes... well, they too can start with a zero (or two; e.g. 00501 - Holtsville - New York) and ZIP9 has a hyphen to split the sections!

    Will track number ever need a character that is not an integer?

    There's no track 1.3. There's no track "B". Is there?
    Last edited by gvee; 10-21-14 at 13:35.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2014
    Posts
    48
    Thank you for your input. The problem is solved and I'm happy with the solution. While you make valid points for defining them as numeric I could also make equally valid points why they should not be. In the end we all have our own styles and I for one chose to follow the advice of my DBMS and Systems Analysis Instructors at DeVry. It has served me well so far.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •