Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports - getting multiple copies (AccessXP)

    Okay I have myself in a mess. And I know it is something simple.

    I am working on an Association database where the members pay dues and may pay one or more WC fees. Dues start in one table and WC in another. I then run an append query, one each, that calculates the cost of the dues and WC fees depending on how they pay, monthly or quarterly etc. Those are sent to intermediate tables tblMonthlyInvoiceA and B. Then I run two append queries from those tables to tblMonthlyInvoiceC. In the C table both dues and WC Fees are now in one Fee field. So in table C the person may have three lines, one for dues and two WC fee lines.

    I created a report, it has two subrpts, to display the invoice information. The report has the Association information in the header. Works like a charm. The next report has the member

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reports - getting multiple copies (AccessXP)

    Have you tried setting the subreport controls on the main form to can shrink, or alternatively setting them to a zero height, and then letting them grow when there is data? In addition, do you have a page break in either the header or the footer of the subreports? Finally there are ForceNewPage properties for each section of both reports and subreports - is one of them set to something other than None?
    Wendell

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Reports - getting multiple copies (AccessXP)

    Fay
    Are you able to post a cut down version of the db to look at. Just the bare minimum of tables/records and the offending report(s)?
    Edited immediately
    Can you do it In Access 2000 format?
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - getting multiple copies (AccessXP)

    Wendall I tried your suggestions and nothing worked. Pat I am going to ship out a copy of the file tomorrow. Ran into another problem. I finally beat that one to death, I think. Don't tell anyone, so it doesn't get back to the database. I think it has a mind of it's own.

    Thanks!
    Fay

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reports - getting multiple copies (AccessXP)

    <<Now to the PROBLEM. If the person has one dues fee and two WC fees there are three reports pages generated. I have tried grouping, hide duplicates, and can shrink. The QUESTION. So what is the correct method to get just one report?>>

    Just to be sure I understand the problem, are you getting 3 pages of one report, or are you getting 1 page for 3 reports each? Also, what is the main/child linkage for each of the subreports?
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - getting multiple copies (AccessXP)

    Bob has 1 dues and 2 WC fees. I receive three pages repeating the same thing for Bob.

    Between rptInvoice and subrptInvoiceMember there is no linkage. rptInvoice has the Association information and subrptInvoiceMember contains members names

    Between subrptInvoiceMember and subrptInvoiceMemberFees both links are MembershipID.

    Thanks for trying. Will have stripped version attachment in a little while.

    Fay

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

    Re: Reports - getting multiple copies (AccessXP)

    You should either have two subreports on the main report, each linked to the main report by master/child link fields, or one subreport on the main report (linked by master/child link fields, and the second as a subreport of the first subreport, also linked.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - getting multiple copies (AccessXP)

    So what do I link to the rptInvoice has nothing to do with the subrptInvoiceMember? There is no common field. But I tried using the SetupID from the Association table (the base for the rptInvoice) and the MembershipID on the subrptInvoiceMember. That didn't work. One way I got only two pages of one person's report the other way I got just the top report.

    Thanks.
    Fay

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

    Re: Reports - getting multiple copies (AccessXP)

    If sbrptInvoiceMember can't be linked to the main report, it will display all records from its record source - it won't depend on the record displayed in rptInvoice. If that is what you want, leave it like that. In that case, concentrate on the link between the main report and the second subreport. Perhaps you can post the scaled down version of your database after all?

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

    Re: Reports - getting multiple copies (AccessXP)

    I have downloaded the zip file. I'll look at it, but don't expect an immediate reply (have to do some shopping etc.)

    BTW, you can easily create a "live" link in a post by typing <!t><!/t> before it and <!t><!/t> after it; you can also get these from the 1-Click TagPanel.

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - getting multiple copies (AccessXP)

    <P ID="edit" class=small>(Edited by WendellB on 19-Feb-03 10:46. Activated link)</P>Okay here is the stripped down version of my database. I left all of the material that is needed to generate an invoice.

    The switchboard has four buttons. The Membership shows you how the costs for dues and WC are entered. There are some controls visible that will be hidden prior to deployment. Note: I tried to create this once using one list, a product list. I ran into problems. Not that I can remember what they were at this time.

    The Compile Billing button will take you to the criteria form. Material is taken from the following tables tblMembership, tblAddresses, tblCostsDues, and tblCostsWC. The information is passed through five queries and three tables and ends up appending to two other tables tblMonthlyInvoiceC and tblInvoiceTotal. There is one criteria box that you will receive after clicking the SOGGDA button to pass the criteria. I just need to get it placed on the criteria form.

    The Invoice button brings up the form to specify the date of billing you want to create invoices for. That information is passed to several queries. Notice two to three sheets of the same information for the same person. There is data compiled for two dates 02/17/2003 and 02/18/2003.

    Addition question. What is the best way to get the MonthlyInvoiceTotal from the tblMonthlyInvoiceTotals. I used the Expression builder to do this and received this; =tblMonthlyInvoiceTotals! MonthlyInvoiceTotal which doesn

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

    Re: Reports - getting multiple copies (AccessXP)

    The problem is that the first subreport should display each member only once, but its record source lists individual invoices for each member. You can get around this by removing the amounts from qryInvoiceMember and making it into a Totals query, grouping on MemberID. The PaymentAlreadyMadewithCCC field will probably cause a problem - it is a part of the individual invoice records, so it could have different values.

    I don't know what to do with tblMonthlyInvoiceTotals - there are multiple records for some MemberID's without a way to distinguish them, so which total would you like to return for a given MemberID?

    I have attached a version of your database with only the objects needed for the report. I have removed the problematic items mentioned above. As you can see, each member gets a single page.
    Attached Files Attached Files

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - getting multiple copies (AccessXP)

    Thanks Hans! That all makes sense. Sometimes it is a real issue of not seeing the forest for the trees.

    PS: I hope shopping went well.

    Fay

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

    Re: Reports - getting multiple copies (AccessXP)

    >>PS: I hope shopping went well.
    Well, it was just getting things for dinner. Even geeks have to eat...

Posting Permissions

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