Results 1 to 11 of 11

Access 2007 subreport sorting for purchase order

  1. #1
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6

    Access 2007 subreport sorting for purchase order

    Hello,



    I am having troubles sorting data on a subreport. Basically I'm creating purchase orders (think of a multiple manufacturer distributorship) linked by customer ID. My report has information linked by customer ID and distributors, subreport has products from different distributors.

    When I run the report the distributors show on each individual page like I want but the products from all the distributors show on all the report pages.

    What I want is a purchase order report to show the distributor and their products only and if other products from another distributorship are ordered for them to show on separate report pages all linked by customer ID.

    Im not an access pro FYI. Can anyone help please?

  2. #2
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Have you tried creating a grouping on distributor ID?
    Boyd Trimmell aka Hitechcoach
    Database Architect and Problem Solver
    Microsoft MVP - Access Expert
    25+ years specializing in Accounting, Inventory, and CRM systems
    "If technology doesn't work for people, then it doesn't work."

  3. #3
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6
    Thank you for the reply. I tried grouping the main report and the sub-report on distributor, and it didnt work. Also, I did not put the distributor under its own ID, it is on the product table.

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Sorry, I am having trouble visualizing what you need without more details about the design of the database tables.
    Boyd Trimmell aka Hitechcoach
    Database Architect and Problem Solver
    Microsoft MVP - Access Expert
    25+ years specializing in Accounting, Inventory, and CRM systems
    "If technology doesn't work for people, then it doesn't work."

  5. #5
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6
    Click image for larger version. 

Name:	New Doc 2017-05-19 (1)_1.jpg 
Views:	12 
Size:	197.2 KB 
ID:	28759Click image for larger version. 

Name:	New Doc 2017-05-19 (1)_2.jpg 
Views:	11 
Size:	123.2 KB 
ID:	28758Click image for larger version. 

Name:	New Doc 2017-05-19 (1)_3.jpg 
Views:	12 
Size:	227.2 KB 
ID:	28757

    I really appreciate the help on this. This is the best way to describe it and I hope this helps.
    I have a main intake form with a sub-form. The products come from the sub-form and are put in the sub-report. Everything is linked by OrdersID from the OrdersTbl. The report sub-report are mostly based off the ProductInfoTbl.

  6. #6
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6
    Ok so I just figured this out. I linked the master and child fields on the sub report to OrdersID and ProductDistributor. It distributed them correctly but if more than one product is ordered from the distributor the report is duplicated. Ideas?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    That linking does not make sense to me. According to your schema, there is no direct link between orders and distributors, they are linked through OrderDetailsTbl and ProductDetailsTbl.

    By customer ID do you mean the ID in PatientInfoTbl?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6
    Yes. I was trying to make it simpler without getting into too much detail figured it would be closer to what most people deal with. When I said customer Id in the original post I was referring to the OrdersTbl OrdersID. Everything is based on the OrdersId PK. My schema may be a little jacked since Im a novice and writing this for myself.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    My comment still applies. I don't see how you could link OrdersID and ProductDistributor and expect to get correct output, even though you say it is. If you want to provide db for analysis, follow instructions at bottom of my post.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    Village Idiot is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2017
    Posts
    6
    O& P Database - Copy.zip Maybe this will help. The data entry starts on the OrderIntakeFrm. From there the order form btn goes to the OrderFormRpt. If you enter more than one product from a Manufacturer the report duplicates. Hope this helps. Thank you very much.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,773
    Well, I don't like the setup of OrderIntakeFrm. Review this discussion about editing multiple tables in one form http://www.accessforums.net/showthread.php?t=66220

    As for the report, since you have a subreport for OrderDetails, don't include those tables in the main report and don't include OrdersTbl in the subreport. Master/Child link on OrdersID fields.

    Some dummy orders data would have helped analysis. I did not enter any so not sure if suggestion will resolve issue.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. Creating a Purchase Order
    By bnecrush in forum Access
    Replies: 12
    Last Post: 11-20-2012, 05:27 PM
  2. Replies: 2
    Last Post: 10-01-2012, 05:47 PM
  3. Purchase Order Template
    By kstewart in forum Access
    Replies: 3
    Last Post: 04-25-2012, 12:07 PM
  4. Purchase Order Report
    By jordanturner in forum Forms
    Replies: 1
    Last Post: 10-13-2010, 09:53 PM
  5. how to creat purchase order
    By arctushar@yahoo.com in forum Access
    Replies: 5
    Last Post: 10-07-2010, 07:14 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
  •  
Tech Forums: Microsoft Office Forums