Totals from separate tables

[kfleming]

Do you want to match__If the master has:__MSTRFLD1 MASTRFLD2__xxx yyyy__yyy zzzz ____and the related file has__RELFLD1 RELFLD2__xxx yyyy__xxx zzzz__yyy yyyy__yyy zzzz____Do you want to match both fields so that the relation returns__MSTRFLD1 MASTRFLD2 RELFLD1 RELFLD2__xxx yyyy xxx yyyy__yyy zzzz yyy zzzz____or do you want match one field at a time so that the relation returns__MSTRFLD1 MASTRFLD2 RELFLD1 RELFLD2__xxx yyyy xxx yyyy__xxx yyyy yyy yyyy__yyy zzzz xxx zzzz__yyy zzzz yyy zzzz______Kathleen__R&R Support

[rkhill74]

I didn^t quite understand your example. Let me restate the relationship between the two table.____MSTR is a table with these two key fields: ID and TYPE__RLTN has the same two fields, but it is a transaction table, so there are multiple occurrences of RLTN records for a single MSTR record.____One of the fields in MSTR is an annual amount field. I^ll call the field ELEC. Naturally, it only occurs once.____RLTN contains several amount fields in each record, all of which I want to accummulate. I^ll call these fields AMT1, AMT2 and AMT3.____What I^m trying to accomplish is this:____Total all of the amount fields, broken out by TYPE (listed above). __I don^t want to report any detail, only summary totals. I don^t have a problem with the AMT1, AMT2 and AMT3 fields in RLTN, but since I can^t seem to get a unique link between MSTR and RLTN, I can^t get ELEC to appear on the report, either in a detail or summary line.____Does this help clarify what I am trying to do?____The report summary line should look like this:____Acct ELEC AMT1 AMT2 AMT3__Type__---- ------- ------- ------- -------__ 1 9999.99 9999.99 9999.99 9999.99__ 2 9999.99 9999.99 9999.99 9999.99__ 3 9999.99 9999.99 9999.99 9999.99____Thanks,__Keith Hill____

[kfleming]

Create a new report using MSTR as the master file and then create a calculated field called LINK with the expression:__ID+TYPE __(I am assuming that these are both character fields)__Then create a relation using LINK as the linking field, RLTN as the related table, scan as the relation type and check the Flexlink box in the relate through section. Then click on expression and create the expression:__ID+TYPE ____Sort and group the report by the ACTTYPE field in the master.__Add three totals each which sums an AMT field from the related table.__Set these totals to reset at Group1 ACCTYPE.__Then add a group1 ACCTYPE footer to the report and place the fields ACCTTYPE, ELEC and your 3 AMT totals. This should give you the desired result.______Kathleen__R&R Support