Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Problem (A2K SP3)

    I have a report, rptReceipt2, which has a section Total Paid By. If the rental is paid by only 1
    payment method, the payment is displayed followed by the Amount Paid. If the rental is partially
    paid by a second method,

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Problem (A2K SP3)

    The second database that does not work properly.

    Tom

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Problem (A2K SP3)

    I would do a redesign of your database to include a Payments table. In this way you can specify a subreport based upon this table.

    It also does not matter how many payments are made against a particular rental.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Problem (A2K SP3)

    Many thanks for your suggestion. In the last 4 or 5 years we have never had a rental paid for by more than 2 methods. While I agree that a payment table makes sense long term, I currently want to see if someone can assist with a solution short of a redesign.

    Tom

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Problem (A2K SP3)

    Try changing the INNER JOINs between PaymentMethod tables to LEFT Joins.

    BTW it was the number of payments not the number of payment methods I was concerned with.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Problem (A2K SP3)

    Pat

    I tried changing the inner joins to left joins on the 2 payment method tables but that did not work for me.

    Tom

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

    Re: Report Problem (A2K SP3)

    Does the attached do what you want? (I only implemented patt's suggestion)

    BTW, will tblPaymentMethod and tblPaymentMethod2 ever contain different values? If not, you need only one table.

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Problem (A2K SP3)

    Thanks to both you and Pat. When I compared your SQL to mine, I discovered that I had tblPaymentMethod2 in twice in qryReceipt2 whch caused the problem.

    The values in the Payment Method tables will always be the same. Since 1 payment can have 2 different payment methods, I thought I had be able to distinguish between them by using PayID and PayID2. TblRental saves both PayID numbers

    Tom

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

    Re: Report Problem (A2K SP3)

    Here is a version that uses only tblPaymentMethod. It has been added to qryReceipt2 twice, once for each PayID field.

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Problem (A2K SP3)

    Thanks. I understand what you mean now. I still have PayID and PayID2 but use the one table to display the pay methods.

    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
  •