Help with dependent values

[ceejsing]

I'm building a database to keep track of expenditures for a grant I manage. Every expenditure has a budget code. Some of these expenditures have another cost associated with them, either an indirect cost or a fringe benefit cost, which is a percentage of the expenditure. This cost has it's own budget code. After calculating this cost for each expenditure in a query, how do I relate it to it's own budget code?



I have separate tables for the expenditures and the budget codes and a query that calculates the indirect cost for each expenditure.


For example:

I have a supply purchase for $3,500 in budget code 25.
My query calculates that the indirect cost for this is $175.00.
How do I get that $175 to associate with budget code 60?

Any help would be appreciated.

[June7]

Hard to say without more info about your schema. How many budget codes can be associated with a purchase? How are you associating code 25 with the purchase?

Need another table?

ExpenseBudget
ExpenseID
BudgetCode

Records would be like:
Purch1 25
Purch1 60

The $175 would not be saved but calculated as needed.

[ceejsing]

Ok. My database is a lot more complicated than I made it seem in my first post. Since it may help, I'm going to elaborate.

The following tables are relevant:

-An RF Codes table that has one type of budget code I need.
-A State Codes table that has the other budget code I need.
-A Budget table that stores our budget line by line with lookup columns for the RF Code and for the State Code.
-An OTPS encumbrances table that stores our OTPS expenditures, also with a lookup column for the RF Code and State Code.
-A Personnel encumbrances table that has our Personnel expenditures, also has a lookup column for the RF Code and State Code.

Queries:

-A query that determines the indirect cost, 5%, for each OTPS expenditure that doesn't have an RF code of 7900.
-A query that determines the fringe benefit for each Personnel expenditure, 38% or 9.5% depending on their employment status, and also calculates the indirect cost of 5%.

If I try to run a query to tell me the total budget of each RF code, the sum of OTPS expenditures for each, and the Personnel Expenditures for each, and calculate the remaining amount, my query will be incomplete because the indirect costs and fringe benefits for every expenditure are calculated in the other queries, but not associated with their correct budget code. This is what I don't know how to do.

[June7]

Budget codes 25 and 60 are both RF codes? This means budget items and encumbrances can have multiple RF codes? So either have another 'junction' table as I suggested to associate all RF codes with a budget or encumbrance record or somehow do a lookup for the correct RF code and calculate as needed. For instance, is the supply purchase encumbrance indirect cost code always 60?

[ceejsing]

Thank you for replying to my posts. I'm sorry I'm not explaining this very well. Access is relatively new for me so I'm missing some key vocabulary. On the flip side, my budget stuff is second nature to me and I forget that not everyone does it in their sleep lol

Yes, 25 and 60 are both RF codes. Budget lines only have one RF code each. Expenditures have only one RF code each, but they have a related indirect cost that has it's own RF code, which is 60.

In my budget, I would have the following columns:

Department
Category
RF Code
State Code
Amount

and say four rows that look like

Accounting-Supplies-25-6200-$20,000
Accounting-Counselor-30-5412-$38,000
Accounting-Fringe Benefits-58-$14,440
Accounting-Indirect-60-9000-$3,622

If you add up the first 3 and take 5%, you get the indirect, which is $3,622.

In my OTPS encumbrances I would have the same columns with the addition of:

Vendor
Order Date

So I might have an encumbrance that looks like this:

Accounting-Supplies-25-6200-$3,500-Staples-8/4/11

So I recorded the cost to RF code 25 for this purchase and I have a query to calculate the indirect cost. It calculates $175, but I have no idea how to get that number to associate with code 60. The only way I can do it at this point is to just manually add another record to the encumbrances like:

Accounting-Indirect-60-9000-$175

I am hoping someone can point me to another way because adding 2 records, in some cases 3 records, instead of 1, will get very aggravating very quickly.

[June7]

I have nothing to add to the suggestion I presented in earlier post, sorry.

[orange]

There is a general accounting data model here
http://www.databaseanswers.org/data_...tems/index.htm
that may be of some help.
I am not a finance/accounting person, but I think a model of your data should identify how the budget, RF codes etc interrelate. As you said, you know the accounting side and can do it in your sleep. Most of us don't and it seems there are some dependencies that exist that are not clearly identified. The issue is to ferret out how these fit together.
What exactly is the definition of an indirect cost? Where does

If you add up the first 3 and take 5%, you get the indirect, which is $3,622

- come from?