Results 1 to 12 of 12
  1. #1
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7

    Only Show Subreport with Data

    I think this is simple, but...

    Main report: CustomersDB as record source
    SubReport: OrdersDB as record source
    Linked fields: CustomerID

    Everything works as expected except the report displays all the customers from CustomerDB even if they do not have any orders.

    How do I filter the report so that only customers with orders will print. Also, what about filtering on orderdate?



    Many thanks,
    John

  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,816
    Use an INNER join instead of the LEFT or RIGHT OUTER or set criteria under OrderID field of Is Not Null.

    What about filtering on orderdate? You can, but with what criteria?
    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
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    Thanks for the response. The INNER join solved the problem of showing customers with no orders. If a customer has two or more orders the name is listed twice? For example:
    CustomerA
    Order 123

    CustomerB
    Order 222
    Order 333

    CustomerB
    Order222
    Order333

    CustomerC
    Order444

    Thanks for any help.

    John

  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,816
    That looks odd. Do you want to provide the project for analysis?
    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
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    Thanks for the response. The INNER join solved the problem of showing customers with no orders. If a customer has two or more orders the name is listed twice? For example: CustomerA

  6. #6
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    I would be glad to. What do I need to do?

    John

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Make copy if need to remove confidential data, run Compact & Repair, zip if still large, up to 2mb zip can be attached 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.

  8. #8
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    The confidentiality of the information is a real concern. This is really a application to track and schedule volunteers rather than company/orders. Is there somewhere I can get some sample first name last name date?

    John

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Read #7 again. Don't care about real data. Dummy records will serve. Get sample first name and last name data? Make it up, like Jane Doe, Jack Doe or just delete all name, address, phone number, SSN fields. They are irrelevant to the issue.
    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.

  10. #10
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    Here is the file. The report "rptScheduledByPerson" shows the problem. I am sure i have made a simple error. Thanks for your help.

    JohnFoodPantryVolunteers Test.zip

  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,816
    You have an unnecessary subreport. Modify the main report RecordSource to include the job title. Set the LFirst textbox HideDuplicates property to Yes. Include the Page footer and move the line control there.
    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
    jkirkman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    7
    Thank you very much.

    John

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

Similar Threads

  1. Getting data in 2 columns in subreport
    By opopanax666 in forum Reports
    Replies: 2
    Last Post: 01-27-2012, 01:56 AM
  2. Replies: 9
    Last Post: 10-12-2011, 02:34 PM
  3. Subreport will not show specific data
    By mitchy1111 in forum Reports
    Replies: 1
    Last Post: 05-18-2011, 07:36 AM
  4. Replies: 3
    Last Post: 06-23-2010, 02:02 PM
  5. Show Hide Subreport / acViewReport
    By BigBear in forum Reports
    Replies: 4
    Last Post: 03-30-2009, 06: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