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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

There is a problem with the first query I posted, it wont handle if there is not a record with the first street name < second street name.

To solve this problem you need a query, qryCrashes_Streets, that lists all the streets FirstStreet and SecondStreet so that the FirstStreet is alphabetically before SecondStreet. This then gives you all possible StreetNames. You can then outser join the tables and add the crashes.

Cheers, Andrew
     IIf([C].[FirstStreet]>[C].[SecondStreet],[C.SecondStreet],[C].[FirstStreet]) AS FirstStreet
   , IIf([C].[FirstStreet]>[C].[SecondStreet],[C.FirstStreet],[C].[SecondStreet]) AS SecondStreet
FROM tblCrashes AS C;
SELECT C.FirstStreet
     , C.SecondStreet
     , A.Crashes
     , B.Crashes
     , Nz([A].[Crashes],0)+Nz([B].[Crashes],0) AS Total
FROM (qryCrashes_Streets AS C 
      LEFT JOIN tblCrashes AS A 
             ON (C.SecondStreet = A.SecondStreet) 
            AND (C.FirstStreet = A.FirstStreet)) 
      LEFT JOIN tblCrashes AS B 
             ON (C.SecondStreet = B.FirstStreet) 
            AND (C.FirstStreet = B.SecondStreet)
ORDER BY Nz([A].[Crashes],0)+Nz([B].[Crashes],0) DESC;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dudesmashAuthor Commented:
You have saved me from a huge time hassle.  It works!! Also thanks a lot for the prompt response.
Thank you very very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.