By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,306 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Evaluation in Access

anukagni
P: 53
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
Jan 16 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 16 '07 #2

anukagni
P: 53
would u able to explain me in detail...


please..

anukagni
Jan 17 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
would u able to explain me in detail...


please..

anukagni
What part would you like me to explain?
Jan 17 '07 #4

anukagni
P: 53
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..
Jan 18 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[FundValue]","tblFunds","[FundID]=" & [FundID]) - DSum("[Amount]","tblFundsPerDept","[FundID]=" & [FundID])
  2.  
This will give you the current balance left in the fund.

Mary
Jan 18 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.