Budget report

[chavez_sea]

Hello,


I need help with running a report. I have two tables ,one budget table with the amounts allocated. (Budget have eg. Staff costs code #1, with sub items 1.1, 1.2, 1.3 etc and Expenses code 2, with sub items 2.1, 2.2, etc
I have another table with expenditure. I want to run a report for a specific time period ( I have that part) listing all transactions and showing what is the balance is per main heading. I made a query linking the sub items in both tables, and specifying that it brings all records from the budget on those that match in the expenditure table.
The result is that for every expenditure it brings the amount budgeted, in the end "blowing' up the budgeted figure. I tried using AVG instead of sum but the end overall total budget is still wrong.

Thanks for help.

Cristina (chavez_sea)

[Dal Jeanis]

Actually, I think you don't want an inner join, I think you want full outer join. If they've spent money that wasn't allocated, you want to know. If they've allocated money but spent nothing, you want to know.

Here's what I'd do if I were you.

1) Make a query that correctly calculates the budget by category. Ignore the expenses for now.
2) Make a query that correctly calculates the expenses by category. Ignore the budget for now.
3) Make a query that matches and nets the budget against the expenses by category.

Take it step by step. If you can't do #1, then don't jump to #3. Ask the questions here that tell you how to do it.

[chavez_sea]

what is a full outer join?

[chavez_sea]

Also what I need is a detail listing of all the expenditure transactions with totals and that matched with the budget for the category of expenses. If I understand what you said to do would be s summary for both expense and budget.

This is an example of the report. The budget for inspectors shows up beside each expense
I average the total budget estimate and so it gives me the correct total
STAFF COSTS
	1.5 Inspectors (Engineer)
	1.5 Inspectors (Engineer)
	1.5 Inspectors (Engineer)
	1.5 Inspectors (Engineer)
	budget estimate:
The grand total at the end however is wrong for the total budget

Thanks

[Dal Jeanis]

So, you don't know how to make a budget by category?

Okay, you'll have to post the table structure and the structure of the current query. We'll get you sorted out.

[chavez_sea]

sorry this is the table

This is an example of the report. The budget for inspectors shows up beside each expense
I average the total budget estimate and so it gives me the correct total
STAFF COSTS			Expenditure
	1.5 Inspectors (Engineer)	€408,000.00	€735.24
	1.5 Inspectors (Engineer)		€147.05
	1.5 Inspectors (Engineer)		€147.05
	1.5 Inspectors (Engineer)		€147.05
	budget estimate:	408,000.00	1,176.39	406,823.61
The grand total at the end however is wrong for the total budget

[chavez_sea]

I have one report that I did of summary of expenditure matched with the total budget by category and that works fine. I have to do another report listing the expenditure in detail and showing the the budget , and the balance, and a grand total, like the example above. The Grand total is where the problem occurs

[chavez_sea]

Detail table of budget consumption by revised budget.pdf Detail table of budget consumption by revised budget.pdf

[Dal Jeanis]

Totals look right to me. The subtotals aren't double-included, anyway. Which ones do you think are off?

[chavez_sea]

This is the budget the total is 1,258,355

[chavez_sea]

The first part the staff cost is correct , that is where there was not expenditure.
THE TOTAL FOR THE operating should be $147,955 see below:

	Amount
STAFF	$126,000.00
STAFF	$126,000.00
STAFF	$81,000.00
STAFF	$42,000.00
STAFF	$408,000.00
STAFF	$204,000.00
STAFF	$18,000.00
STAFF	$5,400.00
STAFF	$100,000.00
TOTAL	$1,110,400.00
OPERATING	$5,400.00
OPERATING	$18,000.00
OPERATING	$18,000.00
OPERATING	$40,000.00
OPERATING	$8,000.00
OPERATING	$7,000.00
OPERATING	$15,000.00
OPERATING	$21,600.00
OPERATING	$14,955.00
TOTAL	$147,955.00
GRAND TOTAL	$1,258,355.00

[chavez_sea]

The reason it appears that the budget is not duplicated is because for the detail, I chose the option to hide duplicates, and for the subtotal, as opposed to SUM for the expenditure I put AVG so that is shows up the average which is works out.
=Avg([Programme estimate budget estimate]) =Sum([Sum total of records])(where records represents each payment. The error occurs at the subtotal by OPERATING EXPENSES, thereby also making an error in the TOTAL BUDGET.

Thanks

[Dal Jeanis]

Don't you want those expenses to be negative, so that they net from the remaining budget?

[chavez_sea]

Well they could be, but I subtract them to get my remaining balance. I just need my total budget figures corrected. Do you see the problem?