Finding a missing number

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. 

Answer 1

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.

 

 

Answer 2

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

Answer 3

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])

Answer 4

User410642551 posted

Thanks for the help!