Results 1 to 12 of 12
  1. #1
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338

    sorting and grouping not sorting in reports


    Hello

    I have created a report with report wizard. I did sorting with one field (Company). When i run the report it's not sorting. What i'm i doing wrong????


    Thank You

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The wizard used the report Grouping & Sorting functionality to set sort criteria? Check the G&S settings are as you expect.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Yes the wizard used the report grouping & sorting.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Well, would have to evaluate the project if you want to provide it. Copy, remove confidential data, run Compact & Repair, zip if large, attach to post. Attachment Manager is below the Advanced post editor.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Here is the file.

    Thank you for your help


    Quote Originally Posted by June7 View Post
    Well, would have to evaluate the project if you want to provide it. Copy, remove confidential data, run Compact & Repair, zip if large, attach to post. Attachment Manager is below the Advanced post editor.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The report is actually sorting by the Provider ID, not the name. Since the field is defined as text, instead of number, it uses alpha sort. Run the report and the ID shows instead of name. The Provider Name field in T_MonthlyTotals actually saves the ID from T_DocInfo. The combobox RowSource lookup made it appear that the field held a name, but it doesn't. Remove the Lookup from the table and you will see the ID. This field should be a number datatype so it is the same type as the autonumber ID in T_DocInfo. Otherwise, query joins on those two tables using the ID fields will fail. You need to include T_DocIno in the report's query so that the related name info will be available. If your intent was to join on names, then ProviderName in T_DocInfo should be the primary key and the autonumber field is irrelevant. But warning, basing primary key on names is not advised. What if you have two Joe Smith? Also, convention is to have name parts in different fields for greatest flexibility in data manipulation. It is easier to put together in queries than take apart.

    I NEVER set these properties in tables just to avoid this sort of confusion. Since should not work directly with tables (use queries, forms, and reports) only purpose this serves is when a form is built with wizard or by drag/drop fields from field list, controls will adopt those properties.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Thank you for your reply. I read your reply I’m not sure what to do. I have a lot of queries, forms and reports linked to T_Docinfo. The only reason i build T_Docinfo was to create a lookup column which would avoid my user from entering the same name different ways. What can i do????

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You have a lot of forms and reports linked to T_Docinfo how? The sample project you provided doesn't show that. As I said, joining T_Docinfo to T_MonthlyTotals on the ID will error becaue fields are different type. Joining on the Provider Name fields won't return in any records because the numbers in T_MonthlyTotals won't match any of the names in T_Docinfo.

    First thing is to convert Provider Name field in T_MonthlyTotals to number datatype (and the same for any other tables that have a similar field). Should not be an issue with this because the values are all numbers. Should rename it more accurately to something like DocInfoID.

    Then fix all form and report RecordSource queries to include T_Docinfo by join clause (join type: 'show all records from T_MonthlyTotals and only those from T_DocInfo that match'). This will make name available and can then sort by name instead of ID.

    Your combobox RowSource should not be affected by these changes.

    The alternative is to edit all records to replace the ID number with actual name. Probably not a simple process, depending on how far along your database is. And again, what happens if you have two Joe Smith?
    Last edited by June7; 01-12-2012 at 09:15 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Sorry they are not linked. I only use T_Docinfo with combo box.

  10. #10
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    I don't know how to do join clause. Can you help me with this???

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Building SELECT queries with a single join of two tables is basic Access functionality. Access Help has guidelines for using the query designer and wizards and lots of tutorials on the web. Here is one http://databases.about.com/od/tutorials/l/aaquery1.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    June

    Thank you very much i was able to fix it using queries.

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

Similar Threads

  1. Sorting in Reports
    By tfanara in forum Reports
    Replies: 1
    Last Post: 08-04-2011, 07:30 PM
  2. Replies: 7
    Last Post: 07-10-2011, 06:55 PM
  3. Replies: 9
    Last Post: 01-28-2011, 06:05 PM
  4. Sorting and Grouping
    By mduplantis in forum Queries
    Replies: 3
    Last Post: 07-29-2010, 12:31 PM
  5. Access report with 3 tables -grouping,sorting
    By kartechy in forum Reports
    Replies: 0
    Last Post: 07-22-2009, 09:07 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