Budgets v/s actuals

[DKS]

Hi,

I have 2 queries, first one giving me budgets per year per cost-centre,
second one giving me actuals per year per cost-centre.

I would like to write a third query that gives me the budget-variance per
year per cost-centre. Budget-variance = Budget minus Actuals.

Many thanks in anticipation.

 

[John W. Vinson]

Hi,

I have 2 queries, first one giving me budgets per year per cost-centre,
second one giving me actuals per year per cost-centre.

I would like to write a third query that gives me the budget-variance per
year per cost-centre. Budget-variance = Budget minus Actuals.

Many thanks in anticipation.

Well, we don't know anything about the structure of your tables or your
queries, but a first cut would be to create a new query joining these two
queries by the cost centre and year fields.

John W. Vinson [MVP]

 

[DKS]

the structure of the 2 queries would contain the following

- year (4 digits)
- cost-centre (id field, both queries would contain the same ids, no
duplicates by definition)
- total amount (numeric field, currency)

I will try out your suggestion, but in the meantime if some ideas to come up
to improve on already proposed solution based on above extra info, then feel
free to post those ideas.

Thanks.

 

[John W. Vinson]

the structure of the 2 queries would contain the following

- year (4 digits)
- cost-centre (id field, both queries would contain the same ids, no
duplicates by definition)
- total amount (numeric field, currency)

I will try out your suggestion, but in the meantime if some ideas to come up
to improve on already proposed solution based on above extra info, then feel
free to post those ideas.

I'd change the name of the Year field, that's a reserved word (for the built
in Year([datevalue]) function). That said...

SELECT A.[cost-centre], A.[year] AS ThisYear, B.[year] AS LastYear, A.[total
amount], A.[total amount] - B.[total amoung] AS YearToYearChange
FROM yourtable AS A INNER JOIN yourtable AS B
ON B.[cost-centre] = A.[cost-centre]
AND B.[year] = A.[year] - 1;

will be a good starting point.


John W. Vinson [MVP]