SQL Query in Acces

1

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

Result:
Sum Of dead is blank
Count = 0

What am I doing wrong?

2

You are looking for rows where all of these columns = 1 on the same row.
Can you verify that there are rows in the table that have all of these values set to 1 prior to 1960?

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);
3

Thank you. Someone else noticed that I needed OR not AND.