Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ascending sort (Access 97)

    I have a column with three possible options. Batched, Logged and Completed. When we sort it is either decending or accessending order. Batch, Completed & Logged or Logged, Completed, Batch. How can we sort and print out the order as we want it.... Logged, Batched, Completed.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Ascending sort (Access 97)

    If you are using a query, you could add a new column (say column 1) that looks at the first letter of the data and assigns a sorting variable. For example, If the first letter is L, then 1, B then 2, or 3. Then sort on the first and then second column. Something like this may help. .. of course, you aren't indicating if there are other required columns of data to sort on.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ascending sort (Access 97)

    To implement Gary's answer, enter a new column in your query and enter the following expression :
    SortOrder: Instr("Logged Batched Completed",[YourFieldName])
    and sort on this column.
    If you want to sort in an other way, set the words of "Logged Batched Completed" in the order you want.
    Francois

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Ascending sort (Access 97)

    Another "custom sort" option (besides the InStr method which is a good trick) is to sort by an expression. Example:

    SELECT tblTest.Status, tblTest.Field2
    FROM tblTest
    ORDER BY [STATUS]="LOGGED", [STATUS]="BATCHED", [STATUS]="COMPLETED", tblTest.Field2;

    where "Status" is the field with the 3 options, Field2 is field to be sorted after Status.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Ascending sort (Access 97)

    To reply further, in this type of situation I often use a "lookup" table and add a "custom sort" field. (See attd example.) Your field with the 3 options would be primary key in this table (related to main table, the one being queried). Add "sort" field, can be integer with unique index. Add lookup table to query and sort by SortOrder field in lookup table. If both fields in lookup table have unique indexes and table relationships are set up correctly, in some cases this may be more efficient than sorting by expressions.
    Attached Images Attached Images

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ascending sort (Access 97)

    Sorry meant to get back with this sooner. Tried Gary's and Francios suggestion, gut that is not what I need. It seem that would still have three different reports but they would be sorted by the sort submited.

    What I need is is one report. Where Logged is first, followed by Batch and then Completed. In a normal sort it would be Batch, Completed, Logged or Logged, Completed, Batch . I need Logged, Batch, Complete The report will be broken down by all the CLients, each client will have three groups - Data that is only Logged, Data that is Batched for processing and Data that has been processed and is Completed. Basicly four fields - Client Name, Status, Batch Control Number, and Number of Records in each batch.

    Texaco
    Logged 14322 32,345
    Logged 14323 5,023

    Batched 14312 10,334
    Bached 14354 4,433

    Completed 14251 6,432
    Completed 14322 9,432

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ascending sort (Access 97)

    Daniel,
    Add the column in your query like I wrote in my previous post.
    Open the report in design view.
    Click on the Sort and Grouping button.
    On the first line enter Client Name
    For this Client Name, add a Group Header and put the Client Name textbox in the Group Header.
    On the second line enter SortOrder.
    This will give the desired sort order.

    edit to remove double line
    Francois

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Ascending sort (Access 97)

    To add to Francois reply, you can then use the field for sorting in the report, however, set its visible properties to no to make in invisible.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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