Can A+B also count B+A as one 1

[KayJen]

Can I use a query to count how many times a set of streets appear in a database.
Main Street and Center Street = 4
Center Street and Main Street = 1

These are the same so I would like the count to be 5, but I can't get this to work.

My Table is Accidents with the below fields
BaseStreet
CollisionStreet
TowardStreet
Date
Time
Weather
DayofWeek

I'm trying to count BaseStreet and TowardStreet with CollisionStreet being the common occurance

 

[r937]

run this first and see what it produces:

select BaseStreet as street1
, TowardStreet as street2
from tbl
union all
select BaseStreet,CollisionStreet from tbl
union all
select TowardStreet,BaseStreet from tbl
union all
select TowardStreet,CollisionStreet from tbl
union all
select CollisionStreet,BaseStreet from tbl
union all
select CollisionStreet,TowardStreet from tbl

now save this as a query

now run

select street1, street2, count(*)
from savedquery
group by street1, street2


rudy

 

[KayJen]

Thank you so very much this seems to be the solution I have developed a headache over searching for. Is there a way to add the date field from my table accidents so that I can narrow the search by months and quarters. Again, Thank You.

Sonny

 

[KayJen]

I've figure out the date portion and the query works as you described which again I greatly appreciate, however, if repeats twice, the numbers are accurate though.

Main St and Center St now says 5
as does
Center St and Main St

Is there a way to remove the second occurance, any additional help would be greatly appreciated.

Thanks,
Sonny

 

[r937]

well, in order to "remove" the 2nd occurrence, you need to figure out why you're getting it twice

since it's actually in the data twice, you might want to eliminate one or more of the 6 possible combinations in the UNION query

rudy

 

[KayJen]

Narrow removed all until I have the below query, but still showing duplicates, all other eliminations seems to create problems.

SELECT [BaseStreet] AS street1, [TowardStreet] AS street2, [Date], [MidBlock]
FROM accidents;
UNION ALL select TowardStreet, BaseStreet, Date, MidBlock
from accidents;

Thanks,
Sonny

 

[r937]

it's your data that has duplicates, not your query

if you just want the distinct combinations, use UNION instead of UNION ALL

but then you cannot count the occurrences accurately


rudy

 

[KayJen]

I've tried UNION and the results are the same, the data is in there more than once if and only if there has been more than one accident at that location within the given month, other than that the data is different. I will keep playing with your query as your solution is the closest I have ever been.

Sonny

 

[KornGeek]

Another workaround is to always store the two streets in alphabetical order. That way, Main and Center will always become Center and Main. I've looked at doing something similar before, and this is the easiest solution I've found.

 

[KayJen]

KornGeek I am not currently at work but I will give this solution a try, Thanks for replying...

Sonny