AnsweredAssumed Answered

Execute SQL Select Count with values from checkbox

Question asked by CaitlinMcKelvie on Nov 11, 2013
Latest reply on Nov 11, 2013 by philmodjunk


Execute SQL Select Count with values from checkbox


     I am trying to get a count for the number of members that I have in each different category. Under the member table, there is a field with a checkbox set that allows them to be members of ITA, Affiliates, ITA R.E.A.D., R.E.A.D., R.E.A.D. Affiliates, and Inquiry. They can be members of multiple categories, and most of them are. I have created a table for statistics, that is full of calculation fields to get the number of volunteers in each field. I have been using Execute SQL (Select Count) to get results. So far, it has been very effective, except when dealing with people who have more than one choice selected in the checkbox set. The equation I am using is

     ExecuteSQL ( "Select Count ( * ) from NewContact where Members  =  ?";"";""; GlobFields::MemberType)

     GlobFields::MemberType is a global field that selects one of the values (ITA, Affiliates, ITA R.E.A.D., R.E.A.D., R.E.A.D. Affiliates, or Inquiry) to be counted.

     This calculation works fine if the member only has one checkbox selected, but if two checkboxes are selected, the member is not counted in either. So if I have 5 members, two are only members of ITA, one is a member of ITA, ITA R.E.A.D., and R.E.A.D., and two are members of just R.E.A.D., my calculations show ____ when they should show (_______)

  •           ITA- 2 (3)
  •           ITA R.E.A.D.- 0 (1)
  •           R.E.A.D.- 2 (3)

     What can I do to query to have it count the member if it is a member of the group and something else. I think that the issue might be the equal sign, because it does not equal just the one value, but I'm not sure what I can put in its place to have it still work.