Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Report and SubReport Query Challenge (2k/2003 SR3)

    Merry Christmas to ALL! I hope Santa was good to everyone.

    I have question that relates to a report with subreports. I have a report with four subreports. Three of the subreports is are tabular format and the other is a pie chart. The query for the pie is top 5, the query for one subreport is top 10, and the query for the other two are ALL records. The report works exactly as requested and is well received.

    Now the user wants that report broken down at a different level in the organization (Dept), with one report for each of the different levels of hierarchy.

    To start with, I created a copy of the original, added a field to main form (Dept) and set up the report/subreport child/master link to this field. The two subreports that query ALL records work fine. However, since the queries for the other two are based on the top 5 and 10 respectively, the subreports show only a subset of the Top X records. This means I no longer have a graph or subreport of Top X, but instead only the number of matching records of the Top X. In some cases that is one, two or three and in other cases it is zero records. I am at a complete loss on how to get the subreports to report on the full 5 and 10 records at the dept level.

    Does anyone have any ideas?

    Thanks in advance for assistance.

    Ken

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report and SubReport Query Challenge (2k/2003 SR3)

    You will need to add Dept to the queries of your subreports, and you will need to link the subreports to the main report on that Dept field. That should do it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report and SubReport Query Challenge (2k/2003 SR3)

    Mark,

    Thanks. The department is already a part of the queries for the subreports, and the main report. And the subreports are linked to the main report via Department. The two subreports that query ALL records present the data expected. The two subreports that are Top 5 and 10 show a subset of the 5 and 10. The subset being those records where the department matches.

    I may not be explaining this very well. Thank you for consideration.

    Ken

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report and SubReport Query Challenge (2k/2003 SR3)

    I don't think I understand what is working and what isn't working.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report and SubReport Query Challenge (2k/2003 SR3)

    Mark,

    Thanks for taking time out of your day. I'm sorry for the poor explanation. After reading your post, it prompted another related thought, and I think I may have found a solution.

    In a nutshell I had four subforms on form. Two of the subforms used queries returning ALL records, and two are supposed to return only the top 5 and top 10 records each. The two forms that use queries to return ALL records work great. The two that request only top 5 and 10 were not returning 5 and 10. i think I have run across one of those ID 10 T issues.

    Thanks for helping out.

    Ken

    THANKS for your help.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •