2 Replies Latest reply on Dec 19, 2016 4:26 AM by David Moyer

    Find what's missing

    dannygast

      I did some googling, but could not find an answer, so I hope some of you can help me out.

       

      Here's the thing:

       

      I have 2 tables with a relation.

      Table 1, 'BOX' has the field 'BoxColor' with 3 records: Red, Blue and Green.

      Table 2, 'CONTENT' has the relational field 'BoxColor' and the field 'NumberInBox'. there are several records with different numbers (let's say 1-9) in the different colored Boxes.

       

      Now in the end, I have 3 Boxes with Numbers in it for example:

       

      Red: 1, 3, 3, 5 and 8

      Blue: 3, 2, 8 and 4

      Green: 5, 4, 7, 8, 2, 2 and 1

       

      Now my Question:

      How can I list/find the missing numbers (in the list: 1-9)

      So the answer should be like:

       

      Red Missing: 2, 4, 6, 7, 9

      Blue Missing: 1, 5, 6, 7, 9

      Green Missing: 3, 6, 9

       

      What would be even more nice, count how many each number occurs, and include the 0's

      So:

      ___Red_Blue_Green

      1|....1......0......1

      2|....0......1......2

      3|....2......1......0

      4|....0......1......1

      5|....1......0......1

      6|....0......0......0

      7|....0......0......1

      8|....1......1......1

      9|....0......0......0

       

       

      I hope I made myself clear as my English may be bad ;-)

       

      Thnks in advance.