Results 1 to 7 of 7
  1. #1
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Question Sort report by serial not by group name

    Hi,
    I have similar report as shown in the below example.
    The report is grouped by "GroupX" then sorted by Ser.
    The report didn't match the targeted result because it sort the report by Group Name then by Ser.


    My target is to sort the report by the group which contains the smaller serial first as displayed on the sample.
    Is is possible to have such sort?
    Thank you
    Click image for larger version. 

Name:	Capture.JPG 
Views:	12 
Size:	58.2 KB 
ID:	42334

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried to change the Sort Order on the Report to Ser, then Group?

  3. #3
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Yes I did, because such report has a group's header & footer, it displayed the header before each item & the footer after it.
    Click image for larger version. 

Name:	Capture2.JPG 
Views:	12 
Size:	39.9 KB 
ID:	42335

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Sample1.zip
    Sample DB Attached

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    One way is to use expression for the group criteria instead of Type field.

    =DMin("Ser", "MainTable", "Type='" & [Type] & "'")

    Another is query as RecordSource.

    SELECT MainTable.*, Q.MS FROM MainTable INNER JOIN (SELECT Type, Min(Ser) AS MS FROM MainTable GROUP BY Type) AS Q ON MainTable.Type=Q.Type;

    Now use MS field as grouping criteria in report.

    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
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Thumbs up

    Thank you very much June7 both ways are working perfectly.
    I understand the first but not the second.
    Actually I was looking for a way to generate such field with min "Ser" under each group.
    Thanks again for always helping.

    Just notice "Dmin" function is Variant so the result is not integer & the sort was displayed as text (1,11,2,3,..) so, I added "CInt" function to convert it to integer:

    Sorter: CInt(DMin("Ser","MainTable","Type='" & [Type] & "'"))
    Last edited by ashraf; 07-02-2020 at 12:35 PM.

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

Similar Threads

  1. Sort on a Group Footer in Access Report
    By Alaska1 in forum Access
    Replies: 2
    Last Post: 10-30-2018, 06:35 PM
  2. Replies: 1
    Last Post: 09-08-2015, 11:02 AM
  3. Replies: 3
    Last Post: 04-28-2015, 02:16 AM
  4. Replies: 3
    Last Post: 06-28-2013, 01:38 PM
  5. Replies: 2
    Last Post: 10-18-2012, 06:56 PM

Tags for this Thread

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