Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query leaves out info (2000)

    Hi!

    Today's question...........I have a query with tblTimeSheet, tblPayments, and tblPaymentAllocations. I need to be able to do a report that shows a Billing/Payment history. My problem is that the query only shows data for invoices (called PayID) that have had payment allocations made to them. I need it to show all invoices, whether they have been paid in full, partially paid or not paid at all. I've tried various joins but nothing seems to work. Here is the present SQL:

    SELECT tblAccountDemographics.[Account Name], tblClientDemographics.[Client Name], tblTimeSheet.Date, tblTimeSheet.[Total Billed], tblPayments.[Date Paid], tblPayments.[Amount Paid], tblPaymentAllocations.AmountAllocated, tblPaymentAllocations.PayID, nz(DSum("AmountAllocated","tblPaymentAllocations", "PayID=" & tblTimesheet.Autonumber),0) AS AmtAlloc, [total billed]-[AmtAlloc] AS Balance
    FROM (tblClientDemographics INNER JOIN tblTimeSheet ON tblClientDemographics.ID = tblTimeSheet.ClientID) INNER JOIN ((tblAccountDemographics INNER JOIN tblPayments ON tblAccountDemographics.AccountID = tblPayments.AccountLink) INNER JOIN tblPaymentAllocations ON tblPayments.PaymentID = tblPaymentAllocations.PaymentLink) ON tblTimeSheet.AutoNumber = tblPaymentAllocations.PayID;


    This works fine if payments have made. My goal is to not have to do two separate reports - one showing payment history and one showing unpaid invoices. Is this possible?
    Thanks,
    Alicia

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

    Re: Query leaves out info (2000)

    You will have to use an outer join instead of an inner join. My guess is that you want all records from tblPayments, regardless of whether there are corresponding records in tblPaymentAllocations. Double click the line between these two tables and select the option to include ALL records from tblPayments. You may have to change other joins too to make it work.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query leaves out info (2000)

    Hi Hans,

    I've tried the join you mentioned but got errors which is why I posted. What other join changes would you suggest or should I just experiment?

    Thanks,
    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query leaves out info (2000)

    I tried doing a separate query just using tblPayment and tblPaymentAllocations. Then I did a query using the new query and tblTimeSheet and tblAccountDemographics. After playing around with the joins, I seem to have the info I need. Is that what you would suggest?
    Leesha

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

    Re: Query leaves out info (2000)

    Outer joins can be a problem if you have many tables in a query. The solution is to do it in two stages, like you did. So I think you're OK (as far as I can tell from 3,000 miles away <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query leaves out info (2000)

    Tee hee! Well to be honest, nothing I did worked so I thought //What would Hans or Pat do// .................that's when I thought I'd try the two query approach. Just opened up a whole new bag of possibilities!!!!

    Thanks,
    Leesha

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

    Re: Query leaves out info (2000)

    You are getting dangerous now. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query leaves out info (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Just trying to catch up to the pack!!

    Leesha

Posting Permissions

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