locked
Finding a missing number RRS feed

  • Question

  • User410642551 posted

    I am doing a survey of a group of people for a special demographic research. I need to find what age is missing from this group.

    For example, my report current display as follows for year 2013

    White
    17
    18
    20
    21
    
    Black
    18
    19
    22
    25
    
    
    Asian
    20
    21
    22
    23
    
    
    Prefer Not to Answer
    20
    21
    23
    24 


    I have 2 parameter field which have values ranging from ages 18-30 and year 2012 -2014.

    I need a forumal that will display the group of people who's age I have not researched.

    Need Example data:

    Parameter searching for: age 19 and year 2013

    White
    17
    18
    20
    21
    
    
    Asian
    20
    21
    22
    23
    
    
    Prefer Not to Answer
    20
    21
    23
    24 

    I will need White, Asian, and Prefer not to answer group of people research.

    How can I do this? I am kind of new to crystal reports. 

    Monday, January 13, 2014 1:38 PM

Answers

  • User-993404089 posted

    SOmething like this.

    SELECT  *
    FROM    [table that has all of the numbers] WHERE   age NOT IN (SELECT age FROM [surveytable])

    or this.

    SELECT *
    FROM   [table that has all of the numbers] WHERE  NOT EXISTS
     
    (SELECT *
      
    FROM   [surveytable]
      
    WHERE  [totaltable.age = [surveytable.age])

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 13, 2014 7:34 PM

All replies

  • User-993404089 posted

    In this particular case you could create a simple table with all ages that you want to survey.  Then do an inner join on your table to that table where they do not exist.  The nulls would be your ages that have not survey.

     

     

    Monday, January 13, 2014 1:51 PM
  • User410642551 posted

    Thanks for the reply. The tables are already created and the tables are already joined. I just need a formula for finding the group with the missing age. Thanks

    Monday, January 13, 2014 3:32 PM
  • User-993404089 posted

    SOmething like this.

    SELECT  *
    FROM    [table that has all of the numbers] WHERE   age NOT IN (SELECT age FROM [surveytable])

    or this.

    SELECT *
    FROM   [table that has all of the numbers] WHERE  NOT EXISTS
     
    (SELECT *
      
    FROM   [surveytable]
      
    WHERE  [totaltable.age = [surveytable.age])

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 13, 2014 7:34 PM
  • User410642551 posted

    Thanks for the help!

    Tuesday, January 14, 2014 2:34 PM