Neil, Can you have following data:

Product                   Colour                   Qty
------------------------------------------------------
JKT001                    Black                    20
JKT001                    MS - YO(BLACK)           20

JKT001                    Beige                    1
JKT001                    MS - BE(BEIGE)           15

JKT001                    White                    1
JKT001                    MS - ???????             15

JKT002                    Black                    20
JKT002                    MS - YO(BLACK)           20

JKT002                    Beige                    10
JKT002                    MS - BE(BEIGE)           15

Qty is not important in this case. If you can how you recognise which system color responds to stated color?


>I am having difficulty laying the foundations for a new report. I have a table which lists the colours that we use for a product. In the same table we fire data in from another supplier system which also shows the same colours. From here we can then see the differences on quantity of what we have stated against what the system states. A typical view of data would be:
>
>

>Product                   Colour                   Qty
>------------------------------------------------------
>JKT001                    Black                    20
>JKT001                    MS - YO(BLACK)           20
>JKT002                    Beige                    10
>JKT002                    MS - BE(BEIGE)           15
>

>
>I have the data like above.
>
>What I want to do is arrange the data so that if there is a variance between the colour we have stated against the system then show. All system colours are specified with a MS. Therefore if I run the report I would get the following:
>
>

>Product               Variance
>------------------------------
>JKT002                    5
>

>
>Can anybody help me please?