Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubReport Problems (2000) (2000)

    In my billing database I generate invoices.
    There are two parts to an invoice:
    1st there are hours (hours table) and these are entered based upon a unique contract id.
    2nd there are expenses (expenses table), these are also entered by unique contract id.

    When I put them together in the report, I use the hours as the main report and the expenses as a sub report with master/child links being the contract id.

    Problem: If there are no hours billed for a particular month, then any expenses for that month have no contract id to connect with and show a 0 balance and therefore are not billed.

    Any thoughts??

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SubReport Problems (2000) (2000)

    You Said <<Problem: If there are no hours billed for a particular month, then any expenses for that month have no contract id to connect with and show a 0 balance and therefore are not billed.>>

    Is this due to hours being a null value? If you enter or set the default hours to zero, will this allow the billing to work correctly?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubReport Problems (2000) (2000)

    It is more than a null value. If there is no hourly billing for the month, then it creates no record in the tblInvoiceGeneration. No record means no contract id which gives me nothing with which to connect expenses.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubReport Problems (2000) (2000)

    Sounds like you need a separate Contracts table. Then you could have a report with two subreports - one for hours and one for expenses. Then it wouldn't matter if either one had no entries.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SubReport Problems (2000) (2000)

    I would have to agree with Douglas on this one as you wouldn't need to incur labor charges to have ODC's to bill. Thus, both sections would be independent of eachother.

    In my prior post, I was leading to that you could probably fool the system by inputting labor with zero hours. Thus, you would get zero labor costs that would generate the contract ID to get to the ODC's. You may have to modify the invoice to make the zero hour labor costs invisible so they wont create any questions.
    Regards,

    Gary
    (It's been a while!)

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

    Re: SubReport Problems (2000) (2000)

    To solve your immediate problem you could use the following query as the source of your main report:

    SELECT Expenses.ContractID
    FROM Hours RIGHT JOIN Expenses ON Hours.ContractID = Expenses.ContractID;

    This returns the records for all the desired ContractID's.
    I have included a sample database that has an Hours and an Expenses table both with ContractID as the key.

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

Posting Permissions

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