Formulas and Calculations

Hello,

I'm wondering if any of you can help me. I'm designing a simple Data base
were I can manage my department's budget. I have created three tabels one
indicate all our department codes, the second indicate our expenditure codes,
and the third indicates de expenses and allocations per department. I have
created queries that pull all transactions per department. Now I'm trying to
create two more queries. One that calculates the the sum of allocations
minus de sum of expenses per expenditure code per department. and the second
one that can give me a sumary of allocations and expenses per month per dept.


I am 100% sure this is doable because I have seen it on another dababase but
when I copied the formula on mine it didn't work. Can Anyone help me?? My
table has the following fields:

tblEXPENSES
Date, Index, Expenditure Code, Vendor, Description, Allocation, Expenses

tblINDEX (this is the departments table)
Index, Fund, Title

Both of my tables are related by the index field

On the first query I would like to see all the transactions listed with a
sum of allocation, sum of expenses an a grand total which is sum of
allocation minus sum of allocations.

The second query I would like to see a summary (sum of all allocations and
expenses per month) of each index of its allocations and expenses per month.

Hopefully I didn't make it so complicated and any of you are able to help me.

=?Utf-8?B?em9ueWJlbA==?= wrote in
:

Now I'm trying to
create two more queries. One that calculates the the sum of
allocations minus de sum of expenses per expenditure code per
department. and the second one that can give me a sumary of
allocations and expenses per month per dept.


tblEXPENSES
Date, Index, Expenditure Code, Vendor, Description, Allocation,
Expenses

tblINDEX (this is the departments table)
Index, Fund, Title

Both of my tables are related by the index field

I'm assuming that the PK of the Index table is Index; but what is the PK
of the Expenses table? Is it (Date, Index)?

I am not sure that you have the design set up correctly yet. Surely there
should be one table for Allocations and one for Expenses? Or,
alternatively, one for both of them, but with a separate record for each
(in other words, treating allocations as a negative expence)? Perhaps you
could tell us what you actually put into the Expenses.Allocation and
Expenses.Expenses fields -- are these money amounts or something else?


I am 100% sure this is doable because I have seen it on another
dababase but when I copied the formula on mine it didn't work.


I am 100% sure too, but it's important to understand exactly what your
data are and what they mean in order to get the design right.

Best wishes


Tim F

Hi Tim,

I work for a School we are divided into departments (math, science, etc..)
Each year we give certain amount to each department to cover all their
expenses for the year. To keep track of this expenses I have created a data
were bye the end of the month I input all expenses incurred by each
department. Have created a table where I keep track of all the expenses and
allocations (deposits), but I would like to create a query were it calculates
expenes and allocations per department. and a 2nd query were it calculate the
expenses per month per dept.

am I making sense?

"Tim Ferguson" wrote:

=?Utf-8?B?em9ueWJlbA==?= wrote in
:

Now I'm trying to
create two more queries. One that calculates the the sum of
allocations minus de sum of expenses per expenditure code per
department. and the second one that can give me a sumary of
allocations and expenses per month per dept.


tblEXPENSES
Date, Index, Expenditure Code, Vendor, Description, Allocation,
Expenses

tblINDEX (this is the departments table)
Index, Fund, Title

Both of my tables are related by the index field

I'm assuming that the PK of the Index table is Index; but what is the PK
of the Expenses table? Is it (Date, Index)?

I am not sure that you have the design set up correctly yet. Surely there
should be one table for Allocations and one for Expenses? Or,
alternatively, one for both of them, but with a separate record for each
(in other words, treating allocations as a negative expence)? Perhaps you
could tell us what you actually put into the Expenses.Allocation and
Expenses.Expenses fields -- are these money amounts or something else?


I am 100% sure this is doable because I have seen it on another
dababase but when I copied the formula on mine it didn't work.


I am 100% sure too, but it's important to understand exactly what your
data are and what they mean in order to get the design right.

Best wishes


Tim F

=?Utf-8?B?em9ueWJlbA==?= wrote in
:

Hi Tim,

I work for a School we are divided into departments (math, science,
etc..) Each year we give certain amount to each department to cover
all their expenses for the year. To keep track of this expenses I
have created a data were bye the end of the month I input all expenses
incurred by each department. Have created a table where I keep track
of all the expenses and allocations (deposits), but I would like to
create a query were it calculates expenes and allocations per
department. and a 2nd query were it calculate the expenses per month
per dept.

am I making sense?

Yes, that makes sense, but there are still some unanswered questions.
Although at present you are doing a load of adding-up at the end of the
month, it seems to me that it might be easier to get the computer to do
chores like that:

Expenses
Dept Date Amount WhatFor
==== ========== ------ ---------------
SCI 2006-08-21 34.50 New Books
SCI 2006-08-26 26.95 New Test Tubes
SCI 2006-09-01 -850.00 Yearly Allocation
etc

It should not be too hard to create the query that will tot up the
monthly amounts for you... on the other hand, you might prefer

Note the allocation as a negative expense -- I'm still not clear about
whether this should be a different table altogether. What happens if a
department needs an extra allocation during the course of the year?

Expenses
Dept Month Amount WhatFor
==== ======= ------ ---------------
SCI 2006-08 69.45 New Books
SCI 2006-09 102.25 More new books
etc

which seems to be what you are describing at the moment. For information,
I have double-underlined the likely PK fields (with = rather than -).
However, you talk about having ExpenditureCodes, which would appear to
refer to individual expenditures rather than monthly summaries -- what
happens if you have more than one kind of expenditure in one month?

Hope that is not too confusing!
All the best


Tim F