3 Replies Latest reply: Jun 27, 2011 5:29 AM by Paul Nockolds RSS

    Join 2 tables or....?

    Paul Nockolds

      QV10 SR2

       

      I have the following 2 tables (from a 3rd party source):

       

      Tab1:

      Question            Answer                Choice

      Job Function        District Nurse         01

      Job Function        Practice Nurse        02

      Job Function        Specialist Nurse     03

      Job Function        Clinical Nurse         04

      Job Function        Health Visitor          05

      Specialist Areas   Oncology              01

      Specialist Areas   Diabetes               02

      Specialist Areas   Wound Care           03

      Specialist Areas   A & E                    04

      Specialist Areas   Travel Health          05

      Industry Sector     Primary Care        01

      Industry Sector     Secondary Care     02

      Industry Sector     Private Sector        03

       

       

      Tab2:

      CustomerID    Question            Choice

      1                     Job Function          01

      1                     Specialist Areas     02

      1                     Industry Sector       01

      2                     Job Function          05

      2                     Specialist Areas     03

      3                     Job Function          02

      3                     Specialist Areas     01

      3                      Industry Sector      03

       

      Here we can see that CustomerID 2 did not answer the Industry Sector Question hence no record in table 2 for that question.

       

      I need to be able to display all the customers and their questions and answers but also include Questions they didn't answer, so for example i need to display something like this...

       

      CustomerID    Question                    Answer

      1                    Job Function                District Nurse

      1                    Specialist Areas           Diabetes

      1                     Industry Sector             Primary Care

      2                    Job Function                 Health Visitor

      2                    Specialist Areas           Wound Care

      2                      Industry Sector           <Not Answered>

      3                     Job Function               Practice Nurse

      3                     Specialist Areas          Oncology

      3                      Industry Sector           Private Sector

       

      This shows CustomerID 2 with an Industry Sector of <Not Answered> so the user will be able to select <Not Answered> and see which customers have missed which questions.

       

       

      I have been trying to use some kind of Join in my script but to no avail

       

      Any ideas about how best to tackle this would be much appreciated.