asked on

Microsoft Access: join or intersect or minus???

I have a table with following rows and columns
1stStreet 2ndStreet Crashes
MLK NorthSt 7
NorthSt MLK 1
WillowSt ChaseSt 2
Ag Dr AlpineSt 3
ChaseSt WillowSt 5

I want the results(total crashes) as follows:
MLK NorthSt 8
WillowSt ChaseSt 7
Ag Dr AlpineSt 3

So basically I want tthe sum of crashes at an intersection. For the above example I have added all crashes for MLK / NorthSt, Willow/ChaseSt, and AgDr/AlpineSt. Since the streetnames 1 and 2 are varying but still considered to be the same intersection, I dont know how to write a query in MS Access. Any help will be of great use to me.
Thanks a lot.

TextReport

Here you go, tried and tested
Cheers, Andrew

SELECT A.FirstStreet
     , A.SecondStreet
     , A.Crashes
     , B.Crashes
     , Nz([A].[Crashes],0)+Nz([B].[Crashes],0) AS Total
FROM tblCrashes AS A 
    LEFT JOIN tblCrashes AS B 
    ON A.SecondStreet = B.FirstStreet 
    AND A.FirstStreet = B.SecondStreet
WHERE A.FirstStreet<[A].[SecondStreet]
ORDER BY Nz([A].[Crashes],0)+Nz([B].[Crashes],0)

Open in new window

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dudesmash

ASKER

Whohooooooooooooo!!
You have saved me from a huge time hassle. It works!! Also thanks a lot for the prompt response.
Thank you very very much
A P