Results 1 to 7 of 7
  1. #1
    TomHolden is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    9

    Report will order/sort 4 columns but not 5. Why?

    Maybe someone can help me out. I'm pretty new to Access and built a nice useful app pretty quickly. I'm impressed by the forms. I like how you can separate the UI from the database with linked tables.

    Anyway.

    My report will only order/sort 4 columns even though the query that underpins the report has 5 ordered/sorted columns.

    My query sorts/orders this way:

    Customers -> Facilities -> Buildings -> Rooms -> Asset Categories

    My report uses that query and sorts this way:

    Customers -> Facilities -> Buildings -> Rooms -> (but won't sort Asset Categories)

    The Asset Categories from the query are no longer sorted alpha ascending after I use the query in the report.

    So for example, Monitors are showing before Cables, which should not be the case.

    I noticed in the Report Wizard you only have the option to sort by 4 columns. Why the limitation? Is there a work around?



    At least the grouping is working right.

    Thanks,

    Tom

  2. #2
    bcofie is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    31
    Tom,
    the records are related, so sorting records in column one will automatically sort column2 and the rest of the columns.there is only so much sorting you can do because columns are related.
    May be, I am not really understanding your question.

    Ben

  3. #3
    TomHolden is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    9
    Thanks for helping me think it through. I thought of a solution.

    I'll concatenate the last two fields together in the query and give them a column alias. Then I'll remove the grouping on ProductCategory (the 5th sorted column) and I'll only sort/order by it... not group by it.

    Like this...

    ProductCategory.Value & " - " & Product.Value as ProductCategoryProduct

    So "Cables" product category will sort before "Monitors" product category.

    "Cables - Product A"
    "Cables - Product B"
    "Monitors - Product A'
    "Monitors - Product B'
    etc...


    Anyway... it's just kind of weird. It the sorting works perfectly (in the query), but when I connect the report to the query, it screws up the last sort. It gets the first four perfect.

    When I run the query, the data sorts exactly how I expect it to, based on 5 columns being sorted, ascending order. Exactly like if you ran it in SQL Server or Oracle.

    When I execute the report, which is based directly on the working query, the report itself screws up on the 5th sort after getting the first four correct first four correct.

    If I were to run this report in Reporting Services or Cognos, I'm 99.9% sure it would be sorted correctly.

    It's tough sometimes for me to communicate in this forum format sometimes. I'd probably have to attach send screen snaps for it to make sense.

    Thanks again,

    Tom

  4. #4
    TomHolden is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    9
    What is difficult to understand, is the query sorts the data perfectly.

    Then when I build and run the report on/against this "perfectly sorting" query, the report mixes up the 5th sort.

    The query gets it right, but the report that is based on the working query, screws it up.

    I thought of a solution. I'm going to concatenate together my "ProductCategories and Products" in the query and give it the column alias "ProductCategoryAndProduct"

    Then I'm going to remove the group on ProductCategory and I'll sort on the new concatenated column "ProductCategoryAndProduct."

    The resulting data will look a little redundant (denormalized) in the report, but it will work and will be in the correct alpha order, which is most important to me.

    (Monitors shouldn't come before Cables - that's what's broken...

    Instead of:

    Monitors
    *****Monitor A
    *****Monitor B
    Cables
    *****Cable A
    *****Cable B

    It will be

    Cables - Cable A
    Cables - Cable B
    Monitors - Monitor A
    Monitors - Monitor B

    That will work. I'll update the post after I give that a try.

    If I can't get it to work, I'll take some screen captures. That might help to clarify. I realize it can be a little difficult to understand me in these technical forum posts.


    Thanks for helping me think it through.

    Tom

  5. #5
    bcofie is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2011
    Posts
    31
    Tom,
    Its kind of weird that the query sorts right by the report based off the query gets screwed. View the query under the RecordSource of the report and make sure that the querys
    sorting is right. Sometimes report wizard screws up.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Reports ignore the sorting of a query and impose the Grouping and Sorting of the report.

  7. #7
    TomHolden is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    9

    Thumbs up

    RuralGuy,

    After 60 seconds of Googling to find where to set the sort on the report columns, (layout view is an easy place) it took 5 seconds to fix. I certainly like the Access layout view for configuring the reports.

    Thanks!

    Tom

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Make TEXTBOXES sort by DESCENDING order
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 12-05-2011, 04:52 PM
  2. Replies: 2
    Last Post: 10-07-2011, 08:13 PM
  3. Multivalue field sort order
    By bdaun in forum Forms
    Replies: 0
    Last Post: 09-05-2011, 12:27 PM
  4. Paradox Sort order for linking tables to access
    By Jimmy in forum Import/Export Data
    Replies: 0
    Last Post: 02-02-2010, 03:13 AM
  5. Sort Order Variation with nulls
    By jonsuns7 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 03:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums