Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: how to customize "order by" in query

    I have a query that pivots a field and makes the values into table column headers (field is called "class"). Let's say the classes are "Algebra" "Geometry" "English1" "Trig" "Literature"

    The options to "order" by are ASC or DESC, but I want to order so that the the new headings are in an order by subject:

    Algebra Geometry Trig English1 Literature

    but I'm not sure how to do this.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Does having another query before your pivot query which sorts it by subject and then have your pivot query base off that other query work?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Posts
    8
    Thanks for your idea, Paul.

    Unfortunately, "subject" is not a field in any table. My solution was to write a make table query afterwards and specify the order in the SELECT statement. I'm not always sure if my solutions are the easiest though they work but I often think there must easiers solutions to writing a lot of separate queries. I'm still learning.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think queries are what makes MSAccess one of the greatest tools and have used them quite often to get what I needed done quickly. I make trade-offs between writing queries such as make tables, etc. and writing code to open recordsets and walk through the recordsets.

    If your make-table query works and it's not taking a lot of time to execute, I'd say go with it. I've gotten quite inventive using queries in several different ways to get to the solution I needed.

    There might be an easier solution but does your solution work (and efficient enough for the users)? I think you'll probably get some other suggestions to what you want to do and there's always trade-offs between each solution. I think your solution would work fine. You can always dive into some code writing if you're concerned about the number of queries you're writing. If you get into the problem of a huge recordset and time of execution, then different solutions come into play where one way might be quicker verses another.

    But I personally don't have any issues with having a lot of queries (as well as many functions) in my application to make it work the way I want it to. I've had some times where I would run 1/2 dozen or even more queries to get to the resultset I wanted. I tend to worry more about field and other changes and how they would affect my queries (i.e. do I have to redesign a lot of them if something is changed and what might be a possible change).
    Last edited by pkstormy; 10-06-06 at 17:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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