Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report with Subreport (2000 latest)

    The database keeps track of weekly donations. Donations may be made in any of 5 categories - Local, Building, M&S, Memorial, Other. Statements are issued twice a year, at the end of June and the end of December. There is no problem with the "end of June" report.

    At the end of December, I have a report that itemizes all weekly donations from July 1 through December 31, and then a subreport that provides only the Totals from January 1 through June 30. The trouble here is that if there are no donations in the July 1 through December 31 period, no Statement is issued.

    Is there a way to produce a report so that if there are donations in the January-June period a Statement will be produced showing $0.00 for July-December and the Totals for January-June?

    I could, of course, produce a report that itemizes all donations for the entire year, but this creates unnecessary paper usage.

    I have tried to put together a report that has only the Donors' name, address, etc., (in other words, just the heading stuff) then an itemized subreport for July-December, plus a Totals subreport for January-June. What happens is that doing it this way produces a duplication of records for the July-December period.

    I have also tried making a Union query, which puts together donations per week in the July-December period + Totals for the January-June period. But the July-December items need a Donation Date, whereas the January-June Totals do not.

    Any suggestion would be appreciated.

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report with Subreport (2000 latest)

    The approach using an itemized subreport for July-December sounds promising. There must be a reason why the records were duplicated. What happens if you open this subreport by itself, from the database window? Do you get duplication then?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with Subreport (2000 latest)

    Hans
    No. When I open the subreport by itself, I don't get the duplicates. But when I attempt to put it in the report, then I get as many duplicates as there are itemized weekly entries. For some reason it seems I am getting some kind of Cartesian product.

    The problem seems to lie in the queries that populate the subreports. There are 2 tables involved - the Main table which holds the names, address, etc., and the Donations table. If I just use the Main table for the query to drive the report, I get reports for everybody that's in the table whether or not they made donations. If I pull in the Donations table as well, and select only records that have Total Donations > 0, that is when I get the duplicate, or triplicate, or more, Donations entries in the itemized subreport when the main report runs.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report with Subreport (2000 latest)

    For this situation (a main report with header information only), you need an unbound main report, i.e. the Record Source must be blank. The Link Child Fields and Link Master Fields properties of the subreport as a control on the main report must be blank too.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with Subreport (2000 latest)

    Hans
    There are 2 subreports. January-June Totals and July-December weekly itemized. There has to be a way to link those two together. So I tried nesting the January-June subreport as a subreport of the July-December subreport. But then I'm back to duplicate, triplicate etc. records. Whenever there is more than one weekly entry to be itemized, I get as many subreports + nested subreports as there are entries.

    I tried setting the query to Unique Records and this made no difference.

    Maybe what I am trying to do isn't doable.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report with Subreport (2000 latest)

    Instead of putting the subreports in the detail section, try putting them in a group footer. You don't need a detail section at all if all the data is in the subreports, and in that case you can put them in the report footer.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report with Subreport (2000 latest)

    Charlotte
    Thanks for the suggestions. I think you found the answer.

    I have kept the report "header" stuff bound to a query that populates the header, moved the two subreports from the Detail to the Group Footer section, and it seems to be working exactly as I want.

    Thanks.

    Tom

Posting Permissions

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