none
Grouping on an Access report RRS feed

  • Question

  • I have a report that needs to group data by section like below:

    The data structure is as follows:

    tbl_customer:

    FirstName

    LastName

    Address(city, st, zip)

    tbl_user

    userid

    username

    tbl_order:

    orderID

    customerID (to tbl_Customer)

    orderno

    tbl_ordershipment

    orderid

    userID (to tbl_users)

    ship date

    ship cost

    comments

    tbl_shiplocation

    shiplocname

    address

    city, st

    zip

    tbl_Product

    productname

    productno

    serialnocolor

    tbl_orderShiploc (joins a shipping location to an order, many to many)

    orderid

    shiplocid

    tbl_orderproduct ((joins a product to an order, many to many))

    orderid

    productid

    qty

    My question comes down to how to group in access so that all shipping locations for an order will show under the order information header and all products will show under the product information header. If I just do shipping location or product, it groups it togehter, but as soon as I add another grouping, it alternates between shipping location and product. I need them all in one group. Would it be using the detail section? or footers?

    I had this done with subforms that grouped the info, but it wouldnt grow dynamically based on the number of rows like in a report.

    the cutomer information will be just one customer per order and ship date/cost comments, will show in footer

    I just need the shipping location and product informtion sections to group  the records in the  many to many tables and have the section grow based on the number of records.

    Report

     

    Customer  

    Name:  «First_Name» «Customer_Name»                                              Phone: «Phone»                                                         

    Service Address:  «Address»                                                       City:       «City»                                   Zip:  «Zip»                          

    User

    ¨  User A           

    ¨  User B

    Order   Information

    Order No: ____

    Shipping   Location

    Address

    City,   St

    Zip

    SHipLocName

    Address

    City/St

    Zip

    Product   Information

    Product

    Product   No:

    Serial   No

    Qty

    Product name

    Prod No

    Serial No

    Qty

    Information:  

    Ship Date:                                           AM / PM                            

    Shipping Cost:                                                  

    COMMENTS

    Tuesday, March 10, 2020 4:03 AM

Answers

  • The sorting and grouping mechanism of a report is hierarchical.  Consequently, as you've found out you cannot achieve the results you want in a single report.  The solution is to use separate subreports for the shipping location and product information.

    You say that you've already achieved your objective with subforms, so you need to replicate this with subreports.  The CanGrow property of the subreport control  in the parent report, and of the relevant sections in the subreport itself should be set to True (Yes).

    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, March 10, 2020 3:43 PM
    Tuesday, March 10, 2020 10:52 AM