Simple question on Queries, but unable to solve it

V

vsoler

I have all the fixed expenses of my office recorded in one table:

Table 1: CostCenters
CostCenterCode
AnnualBudget

In another table, I allocate the costs according to several keys
expressed as %

Table 2: Allocation keys
CostCenterOrigin
CostCenterDestination
Key

Say for example that cost center "3" will work for cost center "7"
30% of its time. In this case the record in table 2 would show:

3
7
30

Knowing that not all cost centers provide services (not all couples of
cost centers exist in table 2), and that some others do not work 100%
of their time for others (they do not allocate 100% of their costs),
how can I combine queries to calculate the annual budget per cost
center after allocations?

Any help that you can provide is highly appreciated.

Thank you
 
T

Tom van Stiphout

On Tue, 4 Dec 2007 01:22:01 -0800 (PST), vsoler

The convention is to express percentages as a fraction of one. Make
Key a Single datatype and enter:
3 7 0.3
When you display a Key value, format the field as Percent and it will
show as 30%.

To sum values, use a Totals query (use the Sigma button). Join the two
tables from CC.CostCenterCode to AK.CostCenterDestination. Select the
CostCenterCode and select GroupBy, and select the AnnualBudget and
Sum.

-Tom.
 
D

Dale Fye

Need more detail.

Assuming you have only two cost centers (1, 2) with annual budgets as below

CostCenterCode AnnualBudget
1 100,000
2 80,000

And CostCenter #2 charges 20% of its time to CostCenter #1; Allocation table
looks like:

CostCenterOrigin CostCenterDestination Key
2 1 .20

What do you want the output of your query to look like? Does the 80K budget
for CostCenter #2 represent 80% of it total budget, and the other 20% comes
out of CostCenter#1's budget CostCenter? If that is the case then the result
might look like (which gets really complicated when you start talking about
multiple origin and destionation allocations):

CostCenterCode Budget Remark
1 80K (100 - 20K)
2 100K (80K + 20K)

Dale
 
V

vsoler

Need more detail.

Assuming you have only two cost centers (1, 2) with annual budgets as below

CostCenterCode AnnualBudget
1 100,000
2 80,000

And CostCenter #2 charges 20% of its time to CostCenter #1; Allocation table
looks like:

CostCenterOrigin CostCenterDestination Key
2 1 .20

What do you want the output of your query to look like? Does the 80K budget
for CostCenter #2 represent 80% of it total budget, and the other 20% comes
out of CostCenter#1's budget CostCenter? If that is the case then the result
might look like (which gets really complicated when you start talking about
multiple origin and destionation allocations):

CostCenterCode Budget Remark
1 80K (100 - 20K)
2 100K (80K + 20K)

Dale
--
Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.











- Mostrar texto de la cita -

Hello Dale,

Your example is perfect for my purpose, except perhaps for your sign
convention:

if CostCenter#2 charges 20% of its time to CostCenter#1, then we have:

a) before allocation:

CostCenterCode Budget
1 65K
2 100K

b) allocation

CostCenterOrigin CostCenterDestination Key
2 1 .20

c) after allocation

CostCenterCode Allocated Budget
1 65K +20 = 85K
2 100K -20%*100 = 80K

Is it possible to get to c) by a simple query given tables a) and b)?

Thank you
 
D

Dale Fye

For 2 cost centers, probably, although your definition of simple and mine
might be different. I'll have to think on that and get back to you this
evening.

--
Don't forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top