Help with budget figures (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

In the database you attached, would you give an example of <>.
Pat

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Kerry we have a problem.
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

Reply | Quote

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!!!

Kerry

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Great news, I can sleep all weekend too.

Pat

Reply | Quote

And have a nice weekend yourself !!!

Reply | Quote

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

Reply | Quote