calculating budget queries

[Co]

Hi All,

I have a financial database holding our companies budget.
Now I want to have some queries telling me how far we are with our
spendings.

We have a budget consisting of several cost posts.
duty car
duty trips
subscriptions
personnel costs etc.

I created a table with the budget for this year holding all the
different cost posts.
I also have a table with all the invoices of this year.

Now I want to let access check the % of the budget already spent per
post and in total.

What do I need for this?

Regards
Marco
The Netherlands

 

[kc-mass]

Hi Marco,

I think you need to show us more details on your table stuctures but in the
interim:

Lets say you have a table that records the expenses that you have planned
for the year:
Something like:

tblPlannedExpense
Fields
PlnExpense
PlnExpDate
PlnTypeExpense

Lets say further that you have a table that records the Actual Expenses
Something like:

tblActualExpenses
Fields
ActExpense
ActExpdate
ActTypeExpense

Then a query like below will give you performance against your plan.



SELECT Sum(tblActualExpense.ActExpense) AS SumOfActExpense,
tblActualExpense.ActTypeExpense, Sum(tblPlannedExpense.PlnExpense) AS
SumOfPlnExpense, Sum([PlnExpense])/Sum([ActExpense]) AS [Percent]
FROM tblPlannedExpense INNER JOIN tblActualExpense ON
tblPlannedExpense.PlnTypeExpense = tblActualExpense.ActTypeExpense
WHERE (((tblActualExpense.ActExpenseDate) Between #1/1/2008# And
#1/20/2008#) AND ((tblPlannedExpense.PlnExpDate) Between #1/1/2008# And
#1/20/2008#))
GROUP BY tblActualExpense.ActTypeExpense, tblPlannedExpense.PlnTypeExpense;

Regards

Kevin

 

[Co]

Hi Marco,

I think you need to show us more details on your table stuctures but in the
interim:

Lets say you have a table that records the expenses that you have planned
for the year:
Something like:

tblPlannedExpense
 Fields
   PlnExpense
   PlnExpDate
   PlnTypeExpense

Lets say further that you have a table that records the Actual Expenses
Something like:

tblActualExpenses
  Fields
     ActExpense
     ActExpdate
     ActTypeExpense

Then a query like below will give you performance against your plan.

SELECT Sum(tblActualExpense.ActExpense) AS SumOfActExpense,
tblActualExpense.ActTypeExpense, Sum(tblPlannedExpense.PlnExpense) AS
SumOfPlnExpense, Sum([PlnExpense])/Sum([ActExpense]) AS [Percent]
FROM tblPlannedExpense INNER JOIN tblActualExpense ON
tblPlannedExpense.PlnTypeExpense = tblActualExpense.ActTypeExpense
WHERE (((tblActualExpense.ActExpenseDate) Between #1/1/2008# And
#1/20/2008#) AND ((tblPlannedExpense.PlnExpDate) Between #1/1/2008# And
#1/20/2008#))
GROUP BY tblActualExpense.ActTypeExpense, tblPlannedExpense.PlnTypeExpense;

 Regards

Kevin

Hi All,

I have a financial database holding our companies budget.
Now I want to have some queries telling me how far we are with our
spendings.

We have a budget consisting of several cost posts.
duty car
duty trips
subscriptions
personnel costs etc.

I created a table with the budget for this year holding all the
different cost posts.
I also have a table with all the invoices of this year.

Now I want to let access check the % of the budget already spent per
post and in total.

What do I need for this?

Regards
Marco
The Netherlands

I did something like this:

SELECT DISTINCTROW invoices.kosten_soort, budget.bedrag AS [Budget],
Sum(invoices.bedrag) AS [Gebruikt], Count(*) AS [Aantal Invoices],
Round([Gebruikt]/(budget.bedrag/100),2) AS [Percentage]
FROM budget INNER JOIN invoices ON budget.soort =
invoices.kosten_soort
GROUP BY invoices.kosten_soort, budget.bedrag;


There's only one problem. I have some negative figures , like -6000.
Access doesn't count them correct.
Instead of doing minus it adds the amount.

anyone know how to fix that?

Marco

 

[kc-mass]

I don't think you want the "COUNT" function in your query.

Hi Marco,

I think you need to show us more details on your table stuctures but in
the
interim:

Lets say you have a table that records the expenses that you have planned
for the year:
Something like:

tblPlannedExpense
Fields
PlnExpense
PlnExpDate
PlnTypeExpense

Lets say further that you have a table that records the Actual Expenses
Something like:

tblActualExpenses
Fields
ActExpense
ActExpdate
ActTypeExpense

Then a query like below will give you performance against your plan.

SELECT Sum(tblActualExpense.ActExpense) AS SumOfActExpense,
tblActualExpense.ActTypeExpense, Sum(tblPlannedExpense.PlnExpense) AS
SumOfPlnExpense, Sum([PlnExpense])/Sum([ActExpense]) AS [Percent]
FROM tblPlannedExpense INNER JOIN tblActualExpense ON
tblPlannedExpense.PlnTypeExpense = tblActualExpense.ActTypeExpense
WHERE (((tblActualExpense.ActExpenseDate) Between #1/1/2008# And
#1/20/2008#) AND ((tblPlannedExpense.PlnExpDate) Between #1/1/2008# And
#1/20/2008#))
GROUP BY tblActualExpense.ActTypeExpense,
tblPlannedExpense.PlnTypeExpense;

Regards

Kevin

Hi All,

I have a financial database holding our companies budget.
Now I want to have some queries telling me how far we are with our
spendings.

We have a budget consisting of several cost posts.
duty car
duty trips
subscriptions
personnel costs etc.

I created a table with the budget for this year holding all the
different cost posts.
I also have a table with all the invoices of this year.

Now I want to let access check the % of the budget already spent per
post and in total.

What do I need for this?

Regards
Marco
The Netherlands

I did something like this:

SELECT DISTINCTROW invoices.kosten_soort, budget.bedrag AS [Budget],
Sum(invoices.bedrag) AS [Gebruikt], Count(*) AS [Aantal Invoices],
Round([Gebruikt]/(budget.bedrag/100),2) AS [Percentage]
FROM budget INNER JOIN invoices ON budget.soort =
invoices.kosten_soort
GROUP BY invoices.kosten_soort, budget.bedrag;


There's only one problem. I have some negative figures , like -6000.
Access doesn't count them correct.
Instead of doing minus it adds the amount.

anyone know how to fix that?

Marco