Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Question Unanswered: Sorting and Grouping:

    In the design view of a report and go into Sorting and Grouping box, where you create your groups’ headers and footers, you also have the option of choosing the sort order (Descending or Ascending). I am wondering how do you customize the order. There is a specific order I want to display (let’s say) my products (it is neither descending nor ascending).

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Add a column to your table with the ordering done in there. Then, in the report, group and order on that.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    Add a column to your table with the ordering done in there. Then, in the report, group and order on that.
    Before I go and do what you’ve suggested, I got a couple questions. If I add a new column with the right orderings, wouldn’t it get sorted when you set it as group on the report, because you have to choose between either descending or ascending in the sort order box? Or is there a way to freeze that column, so it cannot be sorted? At the moment, I don’t see how adding an extra column would solve this problem. Because all my fields I used to generate the report are not sorted in anyway, but they get sorted when I set them as groups on the report. I am working with an obscene set of data, is there an easier way – like an expression or condition that I could implement to get the customized order I want?

    If your way works, and I am working with a lot of records, is there any way I could save some manual effort creating this new field/column?

    Thanks in advance.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tialongz
    Before I go and do what you’ve suggested, I got a couple questions. If I add a new column with the right orderings, wouldn’t it get sorted when you set it as group on the report, because you have to choose between either descending or ascending in the sort order box? Or is there a way to freeze that column, so it cannot be sorted? At the moment, I don’t see how adding an extra column would solve this problem. Because all my fields I used to generate the report are not sorted in anyway, but they get sorted when I set them as groups on the report. I am working with an obscene set of data, is there an easier way – like an expression or condition that I could implement to get the customized order I want?

    If your way works, and I am working with a lot of records, is there any way I could save some manual effort creating this new field/column?

    Thanks in advance.
    No ... Sorting does not work that way. When I said to add the column that was with the understanding that you'd populate it with an ordering sequence that would set how that data would be displayed on the report. And obviously, this column would have to be the PRIMARY (or 1st ordering) group. You can sub-order after that ...

    You can order your data in any way that you desire ... as for a custom expression or condition to order by: It comes back to this: That expression or condition would need to be a column to order on in the report. So, build it or query for it, it's all the same to the report ... Are you querying from a single source? Or is this from multiple sources? The other issue you face is HOW to determine the rules for your ordering (what gets ordered before what and where this logic resides) ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    No ... Sorting does not work that way. When I said to add the column that was with the understanding that you'd populate it with an ordering sequence that would set how that data would be displayed on the report. And obviously, this column would have to be the PRIMARY (or 1st ordering) group. You can sub-order after that ...
    You can order your data in any way that you desire ... as for a custom expression or condition to order by: It comes back to this: That expression or condition would need to be a column to order on in the report. So, build it or query for it, it's all the same to the report ... Are you querying from a single source? Or is this from multiple sources? The other issue you face is HOW to determine the rules for your ordering (what gets ordered before what and where this logic resides) ...
    What happens if this column is not the primary or the 1st ordering group. My first group is by customers, then industries, then businesses, and finally products (with customized sorting). [From general to more specific]

    I hope this does not complicate things.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it does not complicate things.

    make a field ProdSort in the products table and populate it with numbers or strings that give the sort sequence you want. maybe you should leave gaps 100, 200, 300 etc in case new products come along in the future. pull ProdSort in the query for your report. group and sort in the report.

    BTW this ProdSort 'magic number' is undesireable and will take some effort to maintain. don't you have something in your data that provides an inherent sort sequence? how are you going to decide the 'manual' sequence if it is not suggested by something in the data?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by izyrider
    it does not complicate things.

    make a field ProdSort in the products table and populate it with numbers or strings that give the sort sequence you want. maybe you should leave gaps 100, 200, 300 etc in case new products come along in the future. pull ProdSort in the query for your report. group and sort in the report.

    BTW this ProdSort 'magic number' is undesireable and will take some effort to maintain. don't you have something in your data that provides an inherent sort sequence? how are you going to decide the 'manual' sequence if it is not suggested by something in the data?

    izy
    I don't have anything in the data that provides an inherent sort sequence. To shorten the story, just say the database I was given is not work-friendly. There are multiple duplications in the table, and I was not provided with the unique keys to break it a part. (a table with like 35 fields)

    I was given a template that was designed in excel, and I was told to generate reports that follows that excel template's format/design exactly.

    Is there a way, to customize the order of the products without breaking down the main table? Something like left-join queries? tables?

    Side notenot sure if this is going to be helpful in anyway)
    Sorting and Grouping:
    1. CustomerID
    2. Industries
    3. Businesses
    4. Products

    The products field is not unique, different businesses could have offer the same product.

Posting Permissions

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