Calculated Fields

[ABreeze]

Can somebody please help me understand how these fields work or direct to to someplace where I could read about them. I'm having a killer of a time trying to so something that seems so simple. I have a form that tracks applications. Each application may have several projects. I have a subform to track the projects. Each of the projects has a field for chequeAmount - The amount awarded for that particular project. I need to total the cheques for each application (sum of all the projects)- This I have done with a calculated field (sumCheques) in the subform footer. This works. Here's where I encounter problems. I have another table called tblFundingSource which contains a funding source and a grant amount say $1,000,000.00. Now I need to be able to subtract the total of cheques that I obtained for each application from this grant amount. Where and how can I do this and how do I keep track of how much remains of the grant. I am so confused and any direction will be greatly appreciated. Thanks

 

[jimbrooking]

What I would do is write something like this:

DLookup("GrantAmount","tblFundingSource", _
"ID = " & SourceID) - DSum("chequeAmount","tblProjects", _
"ProjectID = " & SourceID)

Terms used are:
tblFundingSource - the table you described
GrantAmount - the field in this table containing the amount of the grant
SourceID - the index in tblGrantSources of the grant you are calculating
tblProjects - the table with all the the grant assignments in it
chequeAmount - the field in this table containing the amount allocated to the project
SourceID - as above

In English: For a given SourceID, or index to a grant, the expression looks up the grant amount, and subtracts the sum of all the cheques allocated to the grant.

You could use this as the control source for a text box on a form or report, or put it in a query.

Jim