0

I have a number of Matters which are in a parent/child relationship. Each has a pointer to their parent matter, or null if they are the parent. Each matter can have a payment made, but all payments in a parent/child relationship are shared between both parent and child. A simplified version of my tables is:

Matter

|  ID       |   Value   | ParentID   |
--------------------------------------
|      1    |    200    |      0     |
|      2    |    200    |      1     |
|      3    |    300    |      0     |
|      4    |    200    |      1     |
|      5    |    800    |      0     |

Payment

|  ID       |   Value   | MatterID   |
--------------------------------------
|      1    |    100    |      1     |
|      2    |     50    |      4     |
|      3    |     20    |      3     |

Note that Matter values are inherited by children (in reality it's just a reference)

I would like to query:

  • The remaining amount on every matter = ([Value] - Sum([All Payments in Parent/Child Relationship))
  • A view of all parent matters, which incorporates the sum of all payments in parent/child relationship.

I am thinking of having a join query showing for each Payment which ParentMatter they apply to, but that will not help me when looking from the child view?

The expected results are:

1. Query only parent matters 
|  ID       |   Value   | TotalRcvd  |
--------------------------------------
|      1    |    200    |    150     |
|      3    |    300    |     20     |
|      5    |    800    |      0     |

2. Query all matters
|  ID       |   Value   | TotalRcvd  |
--------------------------------------
|      1    |    200    |    150     |
|      2    |    200    |    150     |
|      3    |    300    |     20     |
|      4    |    200    |    150     |
|      5    |    800    |      0     |

Where [TotalRcvd] is the sum of all payments that appply to that matter. For example, matter 1 is the parent matter of matters 2 and 4 (ParentID reference in Matter table). Any payments recorded against matters 1, 2 or 4 will go towards the TotalRcvd for each of them. Therefore they each have a total received [TotalRcvd] of 150.

Any help appreciated.

JustNuts
  • 37
  • 2
  • 11
  • please add expected results for your 2 queries given the values in the 2 tables above – FuzzyTree Apr 30 '15 at 02:50
  • edited to show expected values – JustNuts Apr 30 '15 at 03:56
  • 1
    how was the value in the TotalRcvd column calculated? Why it is 300 for the Matter ID = 1? – cha Apr 30 '15 at 05:59
  • I don't think your sample data is helping anyone because you've added a new column that you provided no information on. – Mark C. Apr 30 '15 at 11:13
  • 1
    The reason we ask for a sample output is to understand the logic behind it. if you produce incomplete or incorrect sample you are going no where ;) – Krish Apr 30 '15 at 12:03
  • You're exactly right - I'd made a mistake in my expected output. Amended now with a further explanation. Thanks. – JustNuts Apr 30 '15 at 20:21

0 Answers0