Help with Calc Field summing child table on form

[Rudy16]

Can someone please assist me.

I've included a small example of what I want to accomplish in my application. I want to sum Total Expenses from the Expense table using a Calculated field however, it only sums the 1st entry. I need all entries. Total Expenses now shows 222.00. I want it to show the sum of all the entries in the Expense Table which amounts to 926.00.

I also want to set up the Income table with the same technique.

Can you check to see that I have my "Set" configured correctly and show how to accomplish the above.

The Key is "Event Description"

Thanks,
Rudy

[DaveM]

Re: Help with Calc Field summing child table on form

TotalExpense = total(Exp_Sub_Tbl->Exp_Tot)

[Rudy16]

Re: Help with Calc Field summing child table on form

Thanks Dave.

[DaveM]

Re: Help with Calc Field summing child table on form

extra:

TotalExpense = total(Exp_Sub_Tbl->Exp_Tot)
totalinc = total(Inc_Sub_Tbl->Inc_Tot)
winloss = calc->totalinc-calc->Totalexpense

[Rudy16]

Re: Help with Calc Field summing child table on form

Dave,
I tried your calculation and it worked for the 1st main db record however when I moved to the 2nd main record the child records showed but the expense total showed the 1st total of the first record.

Could you look at the zip db I included and see what I mean?
Rudy

[Rudy16]

Re: Help with Calc Field summing child table on form

Dave,
Let me see if I can be a little clearer.
My main table has 2 fields:
1st field is Event Description (Key)
2nd field is Expense description

my 2nd table has 5 fields which include
- Expense Description
and Expense Total.

I the created a Table Set linking the 2 tables as such:
Parent Table is trip_planner-tbl
Parent Key is event_desc

Child Table is exp_sub_tbl
Child Key = exp_event_desc (Key)

When an Event Description in my browse table changes, I expected to get a total of all expenses for that Event. Instead I get a total of all events in the child table, not just the event that I selected in the browse window.

when I click on a different event in the browse window I see the correct expense entries for all expenses for the clicked event however, the total() calculation total all events. Is this clear as mud??

Rudy

[Tom Cone Jr]

Re: Help with Calc Field summing child table on form

I want to sum Total Expenses from the Expense table using a Calculated field however, it only sums the 1st entry. I need all entries. Total Expenses now shows 222.00. I want it to show the sum of all the entries in the Expense Table which amounts to 926.00.

These are misleading descriptions. It now seems you don't need a total for all entries. ... or for all entries in the Expense table. You need a total of a particular field (exp_tot) summed across only those Expense table records that have the same "Exp_Event_Desc" field value as the current parent table's "Event_Desc" field.

Here's an example of what I think you're looking for, using dbsum().

It really really helps us communicate more efficiently if we all use actual table and field names. There is no field in "Main_tbl2" named "Event Description" for example.

-- tom

[MikeC]

Re: Help with Calc Field summing child table on form

Rudy,
Try

TotalExpense = total(Exp_Sub_Tbl->Exp_Tot,GRP->main_tbl2)

[Rudy16]

Re: Help with Calc Field summing child table on form

Tom,

Your example worked like a charm. I was actuall looking at my "real" app when I tried to explain the problem. I didn't want to send you my entire app because it is getting quiye large, however if I did, I would have been accurate in my description.I'm sorry that I confused you with bad instructions and bad db.

Thanks again for your help.

Rudy

[DaveM]

Re: Help with Calc Field summing child table on form

Rudy,

Sorry, I wasn't online, but glad Tom stepped in. He really knows his way around.

I was and still am getting ready for a court date on Tuesday for the divorce that was supposed to have been complete in 2002.


.

[Keith Hubert]

Re: Help with Calc Field summing child table on form

Hi Guys,

What is the difference between DBSUM() and TOTAL() when both give the same result?

[Tom Cone Jr]

Re: Help with Calc Field summing child table on form

Keith, others may want to supplement this with their own views. The Help file says this about Total():

This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.

However, many experienced developers employ it in form layouts, and have for years. It's even used in AlphaSports, I believe.

Total() runs against the instance of the table that's already open (in the report or form). DbSum() opens a new instance of the table, as does TableSum(). In my own work if I have an index that's available I prefer dbSum() to TableSum() because I think it's faster. If I don't have an index I prefer Tablesum() to Total() unless I'm working in a report layout.

[Keith Hubert]

Re: Help with Calc Field summing child table on form

Tom,

Thanks for your valued explanation. I did not appreciate the explanation in the help file.

I did have a problem with a calc field, which was a total of a browse on a form, it kept loosing it's value and not displaying correctly. I tried all sorts of ways to get the darn thing to refresh, but now I have changed it to DBSUM() and it works just fine.

Thanks again.