Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Help with budget figures (Access 97)

    I hope someone can help me with this problem and that my explanation makes sense.

    In the attached database there are two reports:-

    RECONCILED BUDGET OVERVIEW

    BUDGET COMMITMENT TO DATE

    They are actually the same report, but the

    RECONCILED BUDGET OVERVIEW should only show the totals where is the transactions have been reconciled

    and

    BUDGET COMMITMENT TO DATE is all transactions regardless of whether they are
    reconciled or not.

    "Reconciled" means they have a date entered in the OffCostCentreRpt field in the tbl_Invoices.

    Unfortunately this isnt working for me. When there is an allocation and no expenditure then the amount allocated does not appear and hence the Percentage totals on each section are not correct
    and the Grand total Percentage spent is not correct.

    I suspect it is something very simple I have overlooked. I would be very grateful of any assistance.

    Regards
    Kerry
    Attached Files Attached Files

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

    Re: Help with budget figures (Access 97)

    In the database you attached, would you give an example of <<When there is an allocation and no expenditure>>.
    Pat

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    Hi there

    If you run the other report "Budget Commitment to date" under DX Administration look at 6712 Private Motor Mileage. There is a budget of $50.00 and no expenditure (amount spent). Also straight under that 6713 Travel Expenses $1,200 with no amount spent.

    Hope this is what you are refering to?

    I am most appreciative of your help.

    Regards
    Kerry

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

    Re: Help with budget figures (Access 97)

    In the RECONCILED BUDGET OVERVIEW report the underlying query was excluding the case where there was a record in the qry_BudgetAccounts query but not in the tblInvoices table.

    Change your SQL for the report to:

    SELECT qry_BudgetAccounts.Department, qry_BudgetAccounts.ServicesID, qry_BudgetAccounts.Service, Max(qry_BudgetAccounts.AmtAllocated) AS Allocated, qry_BudgetAccounts.DeptCode, Sum(IIf(IsNull([AmountSpent]),0,IIf([AmountSpent]<>0,[AmountSpent]))) AS Spent
    FROM qry_BudgetAccounts LEFT JOIN tbl_Invoices ON qry_BudgetAccounts.AllocationID = tbl_Invoices.AllocationID
    GROUP BY qry_BudgetAccounts.Department, qry_BudgetAccounts.ServicesID, qry_BudgetAccounts.Service, qry_BudgetAccounts.DeptCode
    HAVING (((Max(qry_BudgetAccounts.AmtAllocated))<>0.0001)) ;

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

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

    Re: Help with budget figures (Access 97)

    Kerry we have a problem. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    You want to select the "Reconciled" entries only, that means you don't want to select the case where you don't have a tblInvoices table entry for a given qry_BudgetAccounts query entry. Isn't that right? So what you already have is correct? Right?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    Dear Pat

    Firstly, I have attached another version of the database with less records in it so that it is easier to see report results on one page.

    I appreciate your patience on this one! It is very difficult to demonstrate.

    If you run and print each of the reports and lay them side by side - look at the amount allocated on the Budget commitment $68,550 Then on the Reconciled report it is $53,500. A discrepancy of $15,050.00

    This is because when I put in the criteria in the Reconciled report, if there has been no expenditure in a line (ie DX6816) the wnole line is not shown, therefore making the Amount allocated total $1,500 short.

    I want to have the Reconciled Report to look like the Commitment report and where there is $0.00 spent, show it as $0.00 and leaving in the amount allocated.

    You can see by the two reports that DV, DR and DD aren't even shown on the Reconciled report. Therefore the allocations are not represented.

    Unless you can think of another way, the only thing I could think of was, on running the Reconciled Rpt to run the qry_OffCostCentreRpt, delete or transfer the transactions that have not been reconciled to temporary table, and then return them back the the tbl_invoices afterward. Now I know that sounds long winded and cumbersome, and I have no idea how to do it, but that would give the correct totals.

    I hope you are still with me after all this!!!

    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Kerry
    Attached Files Attached Files

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

    Re: Help with budget figures (Access 97)

    Hi Kerry,
    There's no need to go that far.

    If you delete the (Is Not Null) criteria leaving the check on the amount <> 0.0001 you will get the desired result.
    However, this means that both reports are then the same as each other. Is this what you want?
    It means that the "Reconciled Budget Overview" report is not a "Reconciled" report anymore.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    Booh Hoo.

    Sorry, Pat, no I dont want the reports to have the same data. But I do want them to LOOK the same. Just changing the criteria from one to the other.

    There is an amount of $1000.00 to DX 6926 that is not reconciled. I want that excluded. It shows up if you run the qry_offCostCentreRpt.

    Another way to look at it is"-
    In the field Amount Spent (Reconciled rept) it needs to show zero if nothing has been spent and only the sum of the amounts spent that have been reconciled. Always leaving the allocation as balance remaining if nothing has been spent.

    It's times like this I wish I have paid more attention in English classes as school!!! It is so hard to explain things.

    Regards
    Kerry <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    Another thought Pat...............

    Is it possible to put something into or change the Expression

    Spent: Sum(IIf(IsNull([AmountSpent]),0,IIf([AmountSpent]<>0,[AmountSpent])))

    that will eliminate the non reconciled transactions from the "spent" field??

    Kerry

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

    Re: Help with budget figures (Access 97)

    Hi Kerry, You were certainly on the way there. Try this query as the source of the Reconciled Budget Overview" report:

    SELECT qry_BudgetAccounts.Department, qry_BudgetAccounts.ServicesID, qry_BudgetAccounts.Service, Max(qry_BudgetAccounts.AmtAllocated) AS Allocated, qry_BudgetAccounts.DeptCode, Sum(IIf(Not IsNull([ExpenditureID]) And IsNull([OffCostCentreRpt]),[AmountSpent],0)) AS NonRecAmt, Sum(IIf(IsNull([AmountSpent]),0,[AmountSpent])) AS TotalSpent, [TotalSpent]-[NonRecAmt] AS Spent
    FROM qry_BudgetAccounts LEFT JOIN tbl_Invoices ON qry_BudgetAccounts.AllocationID = tbl_Invoices.AllocationID
    GROUP BY qry_BudgetAccounts.Department, qry_BudgetAccounts.ServicesID, qry_BudgetAccounts.Service, qry_BudgetAccounts.DeptCode
    HAVING (((Max(qry_BudgetAccounts.AmtAllocated))<>0.0001)) ;

    This calculates a Non Reconciled Amount (new calc) TotalSpent (what you had before) and the Spent now becomes TotalSpent - NonRecAmt.

    HTDI (Hope this does it !!)
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    F A N T A S T I C!!!!!

    I can't believe it! You did it!!!! I have been trying to get this to work for about a month on and off!!

    I cannot thank you enough!!!

    I hope you have the BEST weekend!! Because I will, knowing that I don't have to think about this ANYMORE!

    It makes my database complete.

    Regards Kerry.

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

    Re: Help with budget figures (Access 97)

    <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>
    Great news, I can sleep all weekend too.

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

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

    Re: Help with budget figures (Access 97)

    And have a nice weekend yourself !!!

  14. #14
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Help with budget figures (Access 97)

    <img src=/S/mice.gif border=0 alt=mice width=50 height=25>

    Already poured my first wine! It's a long weekend here in Sth Aust. All we need is sunshine and it will be perfect!

    Kerry

Posting Permissions

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