Formula property of a column

[TomBarrand]

I have a table called FOB and a table called FOB_Line.

In FOB there is a column called FOB_Total. The formula for this column is the SUM of FOB_Line.FOB_Line_Total where FOB.FOB_No equals FOB_Line.FOB_No

What is the syntax that I would put into the formula property of FOB.FOB_Total, if this is possible?

Thanks

 

[RickCole]

Unless there was an overriding performance issue to keep it then the column FOB_Total in FOB should be dropped, as it is calculated data.

Any time you would then like to see the total the following SQL would give you what you want:

Select sum(FOB_Line_Total) from FOB_Line where FOB.FOB_No = FOB_Line.FOB_No

Rick.

 

[TomBarrand]

The calculated field needs to be there because there is another system that relies on this.

Could you give me an example of what the formula would be in this example, as the SQL that I have put in the formula does not work, even though I have validated it using the Query Analyizer.

Thanks

 

[RickCole]

You would not be able to keep a formula in the column FOB.FOB_Total only a value.

If you wanted this value to always reflect the sum of FOB_Line.FOB_Line_Total then the following would apply:

Each time a new row was inserted onto FOB_LINE or FOB_Line.FOB_Line_Total was amended on an existing row. Then the value of FOB.FOB_Total would also have to be updated.

Can you post an example of you SQL.

Rick.