Summarizing Financial Data

[lcs2005]

Hi,

I am new to FM 7.0 and seem to be trying to square a circle. The entries below illustrate my basic problem (the actual data set will run to tens of thousands of records).

I am trying to summarize 4 individual lines of fiscal data into dollar totals that reflect their unique combination of three accounting code elements (i.e., the first three objects in each line below). If each of the three codes agree, the dollar amounts should be totaled. If there is any variation, the charges should be segregated.

The first three accounting code objects are actually separate text fields. I concatenated them, thinking this would create a unique identifier for A(1&2),A(3) & A(4) that would summarize the dollar amounts producing B(1) - B(3). That didn’t work. I can’t think of any other solution. If I am following the right strategy, maybe I am making some other basic mistake just in the layout design? I would greatly appreciate any ideas.

Thanks.

A. Individual Lines (What I have)

1. 564 ABC 59000 $100
2. 564 ABC 59000 $200
3. 570 ABC 59000 $100
4. 570 ABC 61000 $50

B. Summary Lines (What I want)

1. 564 ABC 59000 $300
2. 570 ABC 59000 $100
3. 570 ABC 61000 $50

 

[jaco8867]

Ok, using the following as the field names

item1 item2 item3 amount
1. 564 ABC 59000 $100
2. 564 ABC 59000 $200


Create a summary field being total of amount field (amount must be numeric)

On the layout create a sub summary part when sorted by item3 field. Place item1, item2, item3, and the summary field in this new layout part. Then sort the records by item1, item2, then item3.


This will give you the desired result

Hope that helps