Calculations

[Alylia]

I have as part of a query two fields [Funds_From] and [Funds_To] in which I
enter the movement of funds received from and sent to my company's HQ, and
also the movement of funds between my company's cost centres.

I want a calculated field - [Funds Status] which would return the balance of
funds under the following conditions.

At the start of the financial year when allotments (can also be received
during the FY) are received from HQ to the company for all cost centres,
[Funds Status] should be equal to the amount. If funds are redeployed from
HQ to a specific cost centre, [Funds Status] should increase by the amount.
Conversely,When funds are redeployed back to HQ from one specific cost
centre, [Funds Status] should decrease by the amount transferred. However, if
funds are moved between the cost centres of my company, [Funds Status] should
not be affected.

The entries (in quotation marks) that I make to reflect the above scenarios
are as follows;

Allotments from HQ
[Funds Status] "HQ" [Funds Status] "Allot"

Redeployment from HQ to cost centre Production (PRO)
[Funds Status] "HQ" [Funds Status] "PRO"

Redeployment from cost centre Production (PRO) to HQ
[Funds Status] "PRO" [Funds Status] "HQ"

Redeployment between cost centres - from Production (PRO) to Human Resources
(HR)
[Funds Status] "PRO" [Funds Status] "HR"

Can someone please help me out on the appropriate syntax.

Your help is much appreciated

 

[[MVP] S.Clark]

The syntax to perform a subtraction in a query could be as follows:

Select f1, f2, f2-f1 as MySubtract from tablename where blah, blah, blah...

 

[Alylia]

Thanks for your support.

I would like you to be more clear on your response.

The syntax to perform a subtraction in a query could be as follows:

Select f1, f2, f2-f1 as MySubtract from tablename where blah, blah, blah...

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have as part of a query two fields [Funds_From] and [Funds_To] in which I
enter the movement of funds received from and sent to my company's HQ, and
also the movement of funds between my company's cost centres.

I want a calculated field - [Funds Status] which would return the balance
of
funds under the following conditions.

At the start of the financial year when allotments (can also be received
during the FY) are received from HQ to the company for all cost centres,
[Funds Status] should be equal to the amount. If funds are redeployed
from
HQ to a specific cost centre, [Funds Status] should increase by the
amount.
Conversely,When funds are redeployed back to HQ from one specific cost
centre, [Funds Status] should decrease by the amount transferred. However,
if
funds are moved between the cost centres of my company, [Funds Status]
should
not be affected.

The entries (in quotation marks) that I make to reflect the above
scenarios
are as follows;

Allotments from HQ
[Funds Status] "HQ" [Funds Status] "Allot"

Redeployment from HQ to cost centre Production (PRO)
[Funds Status] "HQ" [Funds Status] "PRO"

Redeployment from cost centre Production (PRO) to HQ
[Funds Status] "PRO" [Funds Status] "HQ"

Redeployment between cost centres - from Production (PRO) to Human
Resources
(HR)
[Funds Status] "PRO" [Funds Status] "HR"

Can someone please help me out on the appropriate syntax.

Your help is much appreciated