Query Results Come Up Short

[KayJen]

I am working on an Accident Collision Program, which requires tracking accidents that occur in an intersection as well as those that occur between two streets in other words Mid Block Accidents. I have three streets, Base Street, Collision Street and Toward Street when I run a query to count Base Street and Toward Streets I come with with four yet I would like for it to give me the right answer, in this case five, because the base street is sometimes the toward street:

Example:
Base Street Toward Street Count
Main Street Assembly Street 4
Assembly Street Main Street 1

There are thousands of accidents, so my query promts for a month and year within which to retrieve this data. I tried a query counting Base St and Toward St and one that counts Toward St and Base St., again I get four. Any help and/or suggestions would be greatly appreciated.

Thanks,
Sonny

 

[Quehay]

Are you looking for the # of times that a street was involved in any of the 3 roles? You could select from the separate columns in each of 3 subqueries joined by union and then select from this query and group/count by name (this will give the street name in any role). Example:

Create UNION query: as street_involvement

select
base as street
from
accidents

union all

select
toward
from
accidents

union all

select
collision
from streets

select
street,
count(street)
from street_involvement
group by street
order by street

This is a rough sketch but it should give you an angle on it..

 

[KayJen]

Thanks I will start working on the angle.

Sonny