Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The screenshot shows the Publisher column is supposed to be sorted by Publisher by means of:
    Private Sub Lbl_Publisher_Click()
    Me.OrderBy = "Publisher, BookTitle"
    Me.OrderByOn = True
    End Sub

    The screenshot shows the Publisher column is actually sorted by PublisherID (autonum). That explains why Apress is placed last.

    Question:
    How to sort the Publisher column by the Publisher, not PublisherID?
    Publisher field is a Lookup field. It consists of 2 columns--PublisherID and Publisher. The 1st field, namely, PublisherID, is hidden.

    Armstrong
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='armsys' post='786631' date='29-Jul-2009 16:13']The screenshot shows the Publisher column is supposed to be sorted by Publisher by means of:
    Private Sub Lbl_Publisher_Click()
    Me.OrderBy = "Publisher, BookTitle"
    Me.OrderByOn = True
    End Sub

    The screenshot shows the Publisher column is actually sorted by PublisherID (autonum). That explains why Apress is placed last.

    Question:
    How to sort the Publisher column by the Publisher, not PublisherID?
    Publisher field is a Lookup field. It consists of 2 columns--PublisherID and Publisher. The 1st field, namely, PublisherID, is hidden.

    Armstrong[/quote]

    It sounds like the Books table has a field Publisher (number), getting values from PublisherID field of the Lookup table.
    You need to add the Publishers table to the Record Source of the form , then sort by tblPublishers.Publisher.
    Make sure the join in the query shows All books and those records from Publisher where the join fields are equal", otherwise books without a publisher will drop off the list.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,

    Thanks for your help. All your assumptions are correct as usual.
    "You need to add the Publishers table to the Record Source of the form..." that would the most interesting statement to me.
    How to add? Do you mean by "joining" the tblBook and tblPublisher by query?
    Is it true that most forms/reports are based on queries, not tables?
    Now I realize that queries are more versatile than tables.

    Armstrong

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should indeed create a query based on tblBook and tblPublisher, with a join on the PublisherID field.
    Sort the query on the Publisher field from tblPublisher.
    Save this query, and use it as the record source for the form.

    It is not possible to say whether "most forms/reports are based on queries, not tables". For many forms and reports, a table suffices as record source, while for others you need a query. It depends on the requirements.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,
    Thanks for your solution, it now sorts as expected.
    Is it possible to place all empty Publishers (PublisherID is null) to the end, instead of the beginning?
    Armstrong
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can add another column to the query to change the sort order:

    [attachment=84902:x.png]
    Attached Images Attached Images
    • File Type: png x.png (20.2 KB, 2 views)

  7. #7
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786644' date='29-Jul-2009 15:51']You can add another column to the query to change the sort order:

    [attachment=84902:x.png][/quote]
    Hi HansV,
    I'm always humbled by the sublime quality and thoroughness of your technical reply.
    This time you even create a sample database to illustrate the solution.
    Thanks a lot.
    Armstrong

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='armsys' post='786640' date='29-Jul-2009 19:14']Is it true that most forms/reports are based on queries, not tables?
    Now I realize that queries are more versatile than tables.[/quote]

    I find that most (but not all) forms and reports are indeed based on queries rather than tables.
    Learning about the power and versatility of queries is, I think, one of the big steps forward in learning about Access.

    But the query does not need to be a saved query!

    If you have a table as the Recordsource of a form, and you click the three dots to the right of the table name in the properties box you get prompted to build a query.
    [attachment=84903:querybuilder.gif]
    You are then taken off to the query builder screen. When you have finished, you close the query builder and return to the form. You are prompted to Save the changes. Say yes.
    You then find that the Record Source is no longer the table, but an SQL statement. An SQL statement is a query in words, but you won't find this query in the list of queries. It is just stored here in the record source of the form.

    [attachment=84904:sqlStatement.gif]

    PS Because of a bug in Access 2007 you should always tab out of the Record Source field of the property box after changing the SQL statement. In the past the changes you made were lost if you did not do this. The latest Service Pack is supposed to have fixed this, but I am not convinced that it has completely.
    Attached Images Attached Images
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,

    Thanks for your showing me a new approach. Saved Query is new to me.
    You tell me where to start (...). You tell me the pitfall as well.
    Thanks a lot.
    Armstrong

Posting Permissions

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