Calculation defined in table to be performed in query

[Dave177]

Hello,

I want to have a table describing the various field names that are used in a database. The data to be held in some of the fields are raw data but other fields will be calculations based on other fields. In my table "tblMyFieldNames" I would want to have two columns:
"FieldName" and "CalculationFormula".
The Calculation field would be blank for those fields that hold raw data but would contain a formula for those fields that require a calculation explaining how the field value is calculated. The calculation would not take place in the table, only be a store so that users can see how a field value is calculated. However, is there a way that such a table can be included in a query so that the query performs the calculation in the field CalculationFormula if it is not blank? This would save having to update the formulas in both the tables and the queries.

Thanks for any help/advice.

David

 

[dhookom]

If your calculations are significantly complex or will need to be updated, you should create a module of formulas to store the functions.

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Hi Duane,

Thanks for your reply.
The formulas themselves aren't very complicated (at the most percentages, subtractions and multiplications). However, I would like the option of just being able to update the formula in the table and any queries base on that value will be changed accordingly.
Do you mind explaining what you mean by a module of formulas to store the functions?

Thanks
David

 

[dhookom]

It would have helped if you would have provided some examples of what/why you wanted to store the expressions in a table. However, if you want to create a column that calculates a net price, you could create a module "modFormulas" with a function:

Public Function GetNet(curUnitPrice as Currency, _
        dblQty as Double) as Currency
    GetNet = curUnitPrice * dblQty
End

Then, in your queries and control sources you can use:
GetNet([UnitPriceField],[QtyField])

If you are really set on storing the formula in a table, you might consider using the Eval() function. I can't imagine the need for this.

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Sorry, I didn't explain it very well. Let's say you were assessing the performance of training centres and you were given monthly data in the form of:

TrainingCentre
NumberOfCandidates
NumberOfQualificationsAchieved
CandidateSatisfaction
MonthlyCostOfCentre

You might want to assess training centres in a number of ways and create some "performance measures" to do this. For example:

CandidateSuccessRate: NumberOfQualificationsAchieved/NumberOfCandidates

or

QualificationEfficiency:
NumberOfQualificationsAchieved/MonthlyCostOfCentre

or

ApportionedCostPerCandidate:
MonthlyCostOfCentre/NumberOfCandidates

You could obviously have the above formulas in the queries but it might be easier to maintain and access/adjust if they were in a table.

Is this any clearer?

David

 

[dhookom]

I'm not sure the work required to create this would be worth it. However, you could store query column definitions in a table like:
[tt]
tblQueryDefs
qdfQDfID autonumber primary key
qdfName name of query
[/tt]
[tt]
tblQueryDefColumns
qdcQDCID autonumber primary key
qdcQDfID link to tblQueryDefs.qdfADfID
qdcCol values of either a field name or formula
qdcAlias Column title
qdcSeq numeric order of the columns
qdcActive yes/no
qdcComment
[/tt]
You could then run some code that would build the SQL statement and apply it to a saved query.

   strQueryName = ...
   strSQL = ...
   CurrentDb.QueryDefs(strQueryName).SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Thanks, Duane. I think you're right and it's probably not worth it. Thanks for your advice though.