This should be trivially easy. I have an Access database of mass murders in the U.S. from 1657 to present. About 1900 incidents. I have a column of dead per incident and a bunch of fields that identify which weapon types were used in each incident: AX, HATCHET, KNIFE and so on. For a variety of reasons, mostly lack of foresight these are defined as INT but really they are boolean: 1 means this weapon was used; NULL means it was not. What I need to do is get a count of incidents and sum of the dead by three categories: incidents using only non-firearms; incidents using only firearms; incident using firearms and any other weapon.
My first attempt was to get the sum of non-firearms dead that used no firearms:
SELECT Sum(Table2.dead) AS [Sum Of dead], Count(*) AS [Count Of Table2]
FROM Table2 where (Table2.UNKNOWN = 1 and Table2.AX = 1 AND Table2.HATCHET = 1 AND Table2.KNIFE = 1 AND Table2.OTHERSHARP = 1 AND Table2.BLUNT = 1 AND Table2.EXPLOSIVE = 1 AND Table2.POISON = 1 AND Table2.STRANGLE = 1 AND Table2.DROWN = 1 AND Table2.ARSON = 1 AND Table2.HANG = 1 AND Table2.OTHER = 1 AND Table2.personal = 1 And Table2.TRAIN = 1 and Table2.Aircraft = 1) and (Table2.Firearm_unknown = 0 or Table2.Shotgun = 0 or Table2.RIFLE = 0 or Table2.PISTOL = 0 or Table2.MACHINE_GUN = 0) AND (Table2.year < 1960);
Sum Of dead is blank
Count = 0
What am I doing wrong?