SUM function not working for lookup field (2003)

[hohepa]

Problem: I have a lookup field that holds numbers (options are 0 or 1). In a query, I would like to use the SUM function to add the values. For example, if I have entered two 1's and three 0's, I would like to get back the value 2. However, when I have two 1's and three 0's, I get back the value "7". The query works find on other fields that are not look-up fields. Any help is greatly appreciated.

[HansV]

How exactly is the lookup field defined, and how do you enter the values - using a combo box, or ...?

[hohepa]

The field I am trying to summarized is the field "altered_consciousness" in table "injury_information_tbl". The field "altered"consciousness" is a lookup field of type Long Integer. The field looks up from the column "altered_consciousness_number" in the table "altered_consciousness_lkup_tbl". The field "altered_consciousness_lkup_tbl.altered_consciousn ess_number" is defined as Long Integer type. The data is entered into injury_information_tbl.altered_consciousness by using a drop-down list.

[HansV]

Your result makes me suspect that 0 and 1 are somehow coded as 1 and 2 (3 times 1 plus 2 times 2 equals 7). Does the altered_consciousness_lkup_tbl table have a primary key field different from altered_consciousness (for example an AutoNumber field), and does the combo box (dropdown list) have 2 columns? If so, the value stored in injury_information_tbl is the primary key. You could get around this by setting the Bound Column property of the combo box to 2. But you'd have to alter already existing values.

[hohepa]

I'm gonna try that. Thanks!!