PDA

View Full Version : Calculated field


ABC123

David
01-21-2005, 02:15 PM
I have a table New_Item_Cost.
I have a 2nd table Part_List_Cost.
I have these as a set, one to many, New_Item_Cost is the parent.
I create a New Item record with a list of parts. In determing the total cost of the parts, I have a calculated field on my form with the total function.

total(Part_List_Cost-"Amount,GRP-"NEW_ITEM_COST)

How can I get this calculated amount into the New_Item_Cost table, into the field cost?
OR Should I go about this in a different way?

Thanks,
David

Bill Warner
01-21-2005, 04:05 PM
You can use DBSUM() to retrieve the sum of values from the child for a given value (key value) in the parent. You could have a field in the parent with this as the calculation in field rules.

David
01-21-2005, 07:03 PM
Hey thanks,

I have a field called cost in my parent.
The fields in my child I would like to total is called amount.

This is from the help

DBSUM( Lookup_Table as C, Index_Tagname as C, Key_Value as C, Lookup_Expression as C )

Is the Lookup_Table as C my child table?
What is the Key_Value?
And in the Lookup_Expression as C , would I use a Total() expression here?

Stan Mathews
01-22-2005, 07:04 AM
Maybe the question is why do you want that total stored in the parent table. The child values are there to be shown on the form when needed.

If you change a child value, it will be immediately reflected on the form, but if the total is stored in the parent table you would have to recalculate all totals to update the parent or have some method to update the parent of the changed child record.

David
01-22-2005, 09:18 AM
Good point, I'm not sure why. I think I have Excelitis. After using Excel for some many years, I always want to put everything in a spreadsheet (browse).
I keep thinking I need to have everything at the table level.
I was trying to see the cost in a browse of the parent only. I'll just use a report.

Thanks Bill and Stan,

David

Al Buchholz
01-22-2005, 11:34 AM
Stan

Actually there are good reasons to have that value at the parent level. Among them are:

1. You can filter and sort a report on the summary value.

2. You can build a summary report at the parent level without having to recalculate from the details.

It becomes an issue of what is the most efficient method to use in that particular system. A stored summary field or a recalc from detail.

Normalize until it hurts. Denormalize until it works.