2 Replies Latest reply on Jun 6, 2011 12:58 PM by FH

    sum accross tables

    FH

      Title

      sum accross tables

      Post

      Hi

      I do have two tables 1. artists and 2. artistic works with a 1:n relation, that is one artist can have many works. There are costs associated with each artist, eg. accomodation, travel and with each work, eg. setup costs, fees, packaging.

      I'm having two kind of sums: One sum for all artist related fields and one sume for each work related fiels. Now I'd like to create a layout where the main sorting is artists and where I'm getting a total for each artist with his/her associated works.

      by using the function sum (total_artist;total_work) I'm getting the sum of the artits and of the last work, but not of the artist and all works.

      See attached screenshot. If you need any additional information, let me know. Thanks in advance.

      example.jpg

        • 1. Re: sum accross tables
          philmodjunk

          Sounds like you need a Summary Report created on a layout based on your artistic works table. Use summary fields instead of Sum and you can use sub summary parts so that you can group your information and compute sub totals. Fields from the Artists table can be added to this layout to fill in the full details you'll need.

          You can use the same summary fields for the artist totals and grand totals. You put them in a sub summary "when sorted by artist" to get a total for the artist. (You can create a "print above" sub summary part for the artist's name, and a "print below" sub summary part for the artist total.)

          For the Work totals, it depends on whether you have a single record for each Artistic Work or multiple records. If you have a single record, put the work fields in the body of the layout to show the totals for that work. If you have multiple records for each work, but them in their own sub summary part "when sorted by artistic work", with the same summary fields you used for the artist totals placed in this sub summary part. You'd then remove the body layout part as your report wouldn't need it in the case were you have more than one record for an artistic work.

          • 2. Re: sum accross tables
            FH

            Hi Phil,

            thanks for your helpful answer. I may add, that since I had multiple works per artist I needed to do the following. I created a new field for a running sum of the work totals, when sorted by artist with the option "begin again for each new group". This allowed to actually sum up the multiple works. The grand total consists of the artists total and the running sum of the work totals.

            thanks a lot for pointing me into the right direction.