AnsweredAssumed Answered

Problem with Subtotal on related data

Question asked by ruhlanma on Aug 21, 2014
Latest reply on Aug 21, 2014 by philmodjunk

Title

Problem with Subtotal on related data

Post

     I've read a couple of posts that appeared to be similar in nature to my problem, but the solutions haven't made sense to me.  Here is my scenario described in simple terms:

      

     I have two tables:

             Tbl1 = Customer

             Tbl2 = Calls

      

     Any given customer can have multiple calls.  However, only the latest call is used in my subtotal.  I have a field in customer called latestcallid    I use this to set a one to one relationship between customer and calls.  It will always contain the call ID of the last call made.

      

     On each call, the rep records simple sales figures.  Product A = $250,  Product B = $100   So in the call table there is a field for productA and productB that contains these numbers.  There is also a field for point-of-sale .  This contains the name of the store where the customer bought that product.   I also created a summary field in this same table keeping a running total of ProductA based upon grouping by point-of-sale.

      

     I created a simple report layout that shows something like this:

      

     Store Name     Customer Name          Product A         Product B

     XYZ Store          John Smith                    $250                 $100

     XYZ Store          Joe Johnson                 $800                  $0

     Subtotal for XYZ Store                             $1050               $100

      

     My report layout is based on the customer table.  When it shows the subtotal line all it does is repeat the last customers numbers.   So instead of the report above, it instead looks like this:

     Store Name     Customer Name          Product A         Product B

     XYZ Store          John Smith                    $250                 $100

     XYZ Store          Joe Johnson                 $800                  $0

     Subtotal for XYZ Store                             $800                  $0

      

      

     I was startled to find that I had to even create these summary fields.  Seems a huge waste of storage.    But even after creating them I am not getting the desired result. 

      

      

      

Outcomes