Variable Calculated values

[NeoPa]

When we have that information, we can look at how we should access the Formula code that the Select statement works from. I would think that it can be stored in the Grades table. Either in place of, or as well as, FormulaID. I can't see any reason for having FormulaID unless it acts as a Foreign Key (FK) into another table for handling formula codes. This may be a good way to do it if there are a large number of them.

Another alternative is to change the code in Compute() to work from the FormulaID numbers instead of the codes. The only reason I suggested strings in the first place is that it's easier to work with and remember if you can assign a string with a meaning, rather than a simple number with no connection to the formula itself.

[patriciashoe]

Sorry for the confusion. Here are the data structures I am using:

Table = [[U]TeacherResources[/U]]
[I]FieldName; Type; Index[/I]
BuidlingID; Numeric
TeacherFTE; Numeric
Enroll_Students; Numeric
Tr_Year; Numeric
SubjectID; Numeric; FK

Table = [[U]Grades[/U]]
[I]FieldName; Type; Index[/I]
SubjectID; Numeric; PK
GradeName; String
BuildingID; Numeric
FormulaID; Numeric
Divisor; nummeric

The tables are linked as noted. Your suggestion to use a numeric for the case statement could do the trick. How about something like this

=IIf([student_enroll]=0,0,myfunction ([formulaID],[enroll_students],teacherfte]

Thanks again, Patti

[NeoPa]

The line would look something like :

=Compute([FormulaID], [Enroll_Students], [TeacherFTE], [Divisor])

You can call it MyFunction if you prefer of course.
There is no need for an IIf() call. Ensure in the code that division by zero is always avoided.

[NeoPa]

I should advise you by the way Patti, that naming can be very important. If you have a PK called [SubjectID] in a [Grades] table then this is likely to confuse people. Not just in a forum like this, but anyone having to deal with the database in a maintenance capacity - including yourself later on.

In a question, it makes it very important for you to make this clear before you start otherwise confusion is almost certain to ensue.

In short, proper naming can be considered unimportant, but never by those with experience.

[patriciashoe]

Thank you!!! Everything is working just fine. I did take note of your advice about proper naming.

Thanks again, Patti

[NeoPa]

I'm really pleased to hear that it's working for you Patti :)

That problem was not a simple one, and is a good illustration of where a bit of lateral thinking can provide real flexibility.