dannygast

Find what's missing

Discussion created by dannygast on Dec 18, 2016
Latest reply on Dec 19, 2016 by David Moyer

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.

Attachments

Outcomes