Results coming through in violation of criterion

[carrr]

I'm baffled by the results of the following query:
__________________________________________________ ______
select events.[report number], [microfilm number], [crash date]
into lowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] between 1 and 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select events.[report number], [microfilm number], [crash date]
into notlowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] > 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select distinct [report number], [microfilm number], [crash date]
into truelowspeed
from lowspeed where [report number] not in (select [report number] from
notlowspeed)

select [report number]
into pedtable
from pedestrians

select [report number],[microfilm number], [crash date]
as [Lowspeed Fatal Crashes - 1994]
from truelowspeed
where [report number] not in (select [report number] from pedtable)

drop table pedtable
drop table truelowspeed
drop table lowspeed
drop table notlowspeed
_______________________________________________

Let me explain what I'm trying to do.

In the first temp table "lowspeed," I'm trying to lump all crashes where a vehicle was doing between 1 and 10 mph.
In "notlowspeed," I'm setting up a table of crashes in which at least one vehicle was going over 10 mph.
By the third gyration, I'm trying to generate "truelowspeed;" which I thought would contain only crashes in which all of the criterion in the first two queries were satisfied and all of the vehicles involved in the crashes were doing between 1 and 10 mph (NO vehicles exceeding the 1 to 10 mph parameter).
The "pedtable" maneuver is simply to pull out any of these crashes in which a pedestrian was involved.
What I'm getting is a lot of crashes in which I have dummy records, several crashes in which one of the vehicles was going over 10 mph, and types of vehicles involved in the crash that should have been excluded by the criterion I specified.

Thoroughly stumped. Would be most grateful for any kind insight/advice.

[Matt]

I will make a few Nostradamus-like predictions about your results.

1) Any "event" that includes one true-low-speed and includes any dummy vehicles is displayed in the results (unless a pedestrian was involved)

2) Any vehicles that are going too fast, are either an unwanted vehilce (like type '09&#39 or are dummy vehicles, or both.

The problem is that the exclusion set (NotLowSpeed in your example) is not inclusive enough (ironic, eh?). The where clause should probably be more like:

where ([dummy record] = 'Y'
OR [estimated mph] > 10)
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [crash injury severity] = 5

I am assuming here that the [dummy record] field has either Y or N in it, and is not null. Hope this helps.
~Matt




------------
carrr at 3/6/2002 4:26:12 PM


I'm baffled by the results of the following query:
__________________________________________________ ______
select events.[report number], [microfilm number], [crash date]
into lowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] between 1 and 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select events.[report number], [microfilm number], [crash date]
into notlowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] > 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select distinct [report number], [microfilm number], [crash date]
into truelowspeed
from lowspeed where [report number] not in (select [report number] from
notlowspeed)

select [report number]
into pedtable
from pedestrians

select [report number],[microfilm number], [crash date]
as [Lowspeed Fatal Crashes - 1994]
from truelowspeed
where [report number] not in (select [report number] from pedtable)

drop table pedtable
drop table truelowspeed
drop table lowspeed
drop table notlowspeed
_______________________________________________

Let me explain what I'm trying to do.

In the first temp table "lowspeed," I'm trying to lump all crashes where a vehicle was doing between 1 and 10 mph.
In "notlowspeed," I'm setting up a table of crashes in which at least one vehicle was going over 10 mph.
By the third gyration, I'm trying to generate "truelowspeed;" which I thought would contain only crashes in which all of the criterion in the first two queries were satisfied and all of the vehicles involved in the crashes were doing between 1 and 10 mph (NO vehicles exceeding the 1 to 10 mph parameter).
The "pedtable" maneuver is simply to pull out any of these crashes in which a pedestrian was involved.
What I'm getting is a lot of crashes in which I have dummy records, several crashes in which one of the vehicles was going over 10 mph, and types of vehicles involved in the crash that should have been excluded by the criterion I specified.

Thoroughly stumped. Would be most grateful for any kind insight/advice.

[carrr]

Matt,

Thanks. I eventually worked through to a fix, but....you were right on in your solution.

carrr


------------
Matt at 3/29/2002 11:15:37 AM

I will make a few Nostradamus-like predictions about your results.

1) Any "event" that includes one true-low-speed and includes any dummy vehicles is displayed in the results (unless a pedestrian was involved)

2) Any vehicles that are going too fast, are either an unwanted vehilce (like type '09&#39 or are dummy vehicles, or both.

The problem is that the exclusion set (NotLowSpeed in your example) is not inclusive enough (ironic, eh?). The where clause should probably be more like:

where ([dummy record] = 'Y'
OR [estimated mph] > 10)
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [crash injury severity] = 5

I am assuming here that the [dummy record] field has either Y or N in it, and is not null. Hope this helps.
~Matt




------------
carrr at 3/6/2002 4:26:12 PM


I'm baffled by the results of the following query:
__________________________________________________ ______
select events.[report number], [microfilm number], [crash date]
into lowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] between 1 and 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select events.[report number], [microfilm number], [crash date]
into notlowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] > 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04&#3 9;,'05','06','07','08& #39;,'10','11','12&#39
and [crash injury severity] = 5

select distinct [report number], [microfilm number], [crash date]
into truelowspeed
from lowspeed where [report number] not in (select [report number] from
notlowspeed)

select [report number]
into pedtable
from pedestrians

select [report number],[microfilm number], [crash date]
as [Lowspeed Fatal Crashes - 1994]
from truelowspeed
where [report number] not in (select [report number] from pedtable)

drop table pedtable
drop table truelowspeed
drop table lowspeed
drop table notlowspeed
_______________________________________________

Let me explain what I'm trying to do.

In the first temp table "lowspeed," I'm trying to lump all crashes where a vehicle was doing between 1 and 10 mph.
In "notlowspeed," I'm setting up a table of crashes in which at least one vehicle was going over 10 mph.
By the third gyration, I'm trying to generate "truelowspeed;" which I thought would contain only crashes in which all of the criterion in the first two queries were satisfied and all of the vehicles involved in the crashes were doing between 1 and 10 mph (NO vehicles exceeding the 1 to 10 mph parameter).
The "pedtable" maneuver is simply to pull out any of these crashes in which a pedestrian was involved.
What I'm getting is a lot of crashes in which I have dummy records, several crashes in which one of the vehicles was going over 10 mph, and types of vehicles involved in the crash that should have been excluded by the criterion I specified.

Thoroughly stumped. Would be most grateful for any kind insight/advice.