Order By

Results 1 to 2 of 2

Thread: Order By

  1. #1
    Para Guest

    Default Order By

    I have a Acces DB with a text field that holds months as text. eg. january,february..etc<BR><BR>I want to do a select SQL statement and order the results by the months as they are in real life..<BR><BR>any idea&#039;s?

  2. #2
    Join Date
    Dec 1969

    Default That's a toughie!

    Truthfully, you screwed up in the table design. You should have just put in numbers for the months and then used Access&#039;s function MonthName to convert the numbers to names for report purposes.<BR><BR>Then, of course, the ORDER BY would also be natural.<BR><BR>You know, the more I think of it, I think this one is worth a table re-design!<BR><BR>You could easily fix the table, using Access:<BR><BR>(1) Add a column named "MonthNumber" (or whatever).<BR>(2) Do 12 UPDATE queries of the form<BR>&nbsp; &nbsp; UPDATE table SET MonthNumber = 1 WHERE MonthName = &#039;January&#039;<BR>&nbsp; &nbsp; UPDATE table SET MonthNumber = 2 WHERE MonthName = &#039;February&#039;<BR>&nbsp; &nbsp; ... etc. ...<BR>(3) Optionally, delete the MonthName column.<BR><BR>But if you can&#039;t do that...<BR><BR>Hmmm...<BR><BR>OKAY... Here we go:<BR><BR>Add a new table to the database ("MonthLookup").<BR><BR>Fields:<BR>&nbsp; &nbsp; MonthName -- Text, PrimaryKey<BR>&nbsp; &nbsp; MonthNumber -- Number<BR><BR>Then fill the table with 12 rows, one for each month, using the obvious values.<BR><BR>Then write your query thus:<BR><BR>SELECT MN.MonthNumber, PD.*<BR>FROM MonthLookup AS MN, PrimaryDataTable AS PD<BR>WHERE MN.MonthName = PD.MonthName<BR>ORDER BY MN.MonthNumber<BR><BR>See it? You use the lookup table to convert the text form of the name, which is essentially un-orderable in any usable fashion, into a number which *is* orderable in the way you want.<BR><BR><BR>

Posting Permissions

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