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.


dudesmashAsked:
Who is Participating?
 
TextReportConnect With a Mentor Commented:
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
qryCrashes_Streets
SELECT DISTINCT 
     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

0
 
TextReportCommented:
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

0
 
dudesmashAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.