Evaluation in Access

[anukagni]

Hello Friends,

This question make's me mad for last two weeks..

I want to create an database for my company and in that database i have to track funding details.. like below..

( All are asumption value & fields)

Fund Name : XXY015 Fund Value : $10,000.00
Fund Name : XXY016 Fund Value : $ 05,000.00

Now iam evauating as

Dept - X get's the fund amount of $ 5000.00 from Fund Name : XXY015 in Jan07

Dept -Z get's the fund amount of $1000.00 from Fund Name : XXY016 in Jan07

This is what iam going to enter in one table ..now i need is when i use the Fund Name :XXY015 next time then the Fund Amount should show as $9,5000.00 . as closing balance...

How can i do this in the backend .. please give me an idea..

anukagni

[MMcCarthy]

Hello Friends,

This question make's me mad for last two weeks..

I want to create an database for my company and in that database i have to track funding details.. like below..

( All are asumption value & fields)

Fund Name : XXY015 Fund Value : $10,000.00
Fund Name : XXY016 Fund Value : $ 05,000.00

Now iam evauating as

Dept - X get's the fund amount of $ 5000.00 from Fund Name : XXY015 in Jan07

Dept -Z get's the fund amount of $1000.00 from Fund Name : XXY016 in Jan07

This is what iam going to enter in one table ..now i need is when i use the Fund Name :XXY015 next time then the Fund Amount should show as $9,5000.00 . as closing balance...

How can i do this in the backend .. please give me an idea..

anukagni

tblFunds
FundID (Primary Key, Autonumber)
FundName
FundValue (opening value only)

tblDepts
DeptID (Primary Key, Autonumber)
DeptName

tblFundsPerDept
FundID (Primary Key, Foreign key to FundID Primary Key)
DeptID (Primary Key, Foreign key to DeptID Primary Key)
Amount (Amount of fund assigned to dept)
DateAssigned (Date fund amount assigned to dept)

Check out this tutorial for assistance.

You can then design a form based on the third table to allow you to assign funds per dept. The fund balance should not be stored as it changes all the time and is a calculated value. However, you can display it on the form with a calculated field and set some validation in code that will not allow an unavailable fund balance to be assigned.

Mary

[anukagni]

would u able to explain me in detail...


please..

anukagni

[MMcCarthy]

would u able to explain me in detail...


please..

anukagni

What part would you like me to explain?

[anukagni]

What part would you like me to explain?

I have already did what u said & i want to know how this value will be didacted and stored in an table ...

Explain me in that area..

anukagni..

[MMcCarthy]

I have already did what u said & i want to know how this value will be didacted and stored in an table ...

Explain me in that area..

anukagni..

The balance value wouldn't be stored. It would be calculated each time the form is opened or changed using the something like the following in the control source of the textbox.

=DLookup("[FundValue]","tblFunds","[FundID]=" & [FundID]) - DSum("[Amount]","tblFundsPerDept","[FundID]=" & [FundID])
 

This will give you the current balance left in the fund.

Mary