Access 2002 - Several levels of Nested inner joins to the same table (vs regular inner joins)?

I have inherited a poorly designed Access 2002 database. I am trying to troubleshoot a few queries, and one query in particular has left me scratching my head. It is nesting several joins to the same table... each join is nested in the previous join rather than just joining to the same table multiple times. I honestly can't even say for sure why this query works - I have never seen a join created this way... Can anyone comment on the effect of these inner joins (performance wise?) and will I see a performance improvement by un-nesting them?

Example:

--Detals cut
FROM tempMapIDs INNER JOIN 
(BenefitMapRiders AS RxDeduct INNER JOIN 
(BenefitMapRiders AS SubAbuse INNER JOIN 
(BenefitMapRiders AS Infertility INNER JOIN 
(BenefitMapRiders AS Dental INNER JOIN 
(BenefitMapRiders AS Chiro INNER JOIN 
(BenefitMapRiders AS Vision INNER JOIN 
(BenefitMapRiders AS Drug INNER JOIN qryOptionsMap ON Drug.NewRiderOption = qryOptionsMap.Drug) 
ON Vision.NewRiderOption = qryOptionsMap.Vision) 
ON Chiro.NewRiderOption = qryOptionsMap.Chiro) 
ON Dental.NewRiderOption = qryOptionsMap.Dental) 
ON Infertility.NewRiderOption = qryOptionsMap.Infertility) 
ON SubAbuse.NewRiderOption = qryOptionsMap.SubAbuse) 
ON RxDeduct.NewRiderOption = qryOptionsMap.RxDeduct) 

ON (tempMapIDs.NewDate = qryOptionsMap.RenewalDate) 
    AND (tempMapIDs.NewPlanOption = qryOptionsMap.PlanOption) 
    AND (tempMapIDs.RxDeduct = RxDeduct.OldRiderOption) 
    AND (tempMapIDs.SubAbuse = SubAbuse.OldRiderOption) 
    AND (tempMapIDs.Infertility = Infertility.OldRiderOption) 
    AND (tempMapIDs.Chiro = Chiro.OldRiderOption) 
    AND (tempMapIDs.Vision = Vision.OldRiderOption) 
    AND (tempMapIDs.Dental = Dental.OldRiderOption) 
    AND (tempMapIDs.Drug = Drug.OldR开发者_如何学JAVAiderOption) 
    AND (tempMapIDs.MapID = RxDeduct.MapID) 
    AND (tempMapIDs.MapID = SubAbuse.MapID) 
    AND (tempMapIDs.MapID = Infertility.MapID) 
    AND (tempMapIDs.MapID = Chiro.MapID) 
    AND (tempMapIDs.MapID = Vision.MapID) 
    AND (tempMapIDs.MapID = Dental.MapID) 
    AND (tempMapIDs.MapID = Drug.MapID)
) 

That is just the way Access tends to write queries when you use the visual query designer to create them. It shouldn't perform any worse than the equivalent syntax you are used to.

Create a new query with 2 or three joins using the visual designer and you will see the same syntax.

I think I know where you are coming from. I'd prefer to write the same joins in a way that keeps the ON clauses close to the JOIN clauses for ease of reading and thus maintenance e.g. :

FROM 
     tempMapIDs 

     INNER JOIN qryOptionsMap 
        ON tempMapIDs.NewDate = qryOptionsMap.RenewalDate
           AND tempMapIDs.NewPlanOption = qryOptionsMap.PlanOption

     INNER JOIN BenefitMapRiders AS RxDeduct 
        ON tempMapIDs.MapID = RxDeduct.MapID
           AND tempMapIDs.RxDeduct = RxDeduct.OldRiderOption
           AND RxDeduct.NewRiderOption = qryOptionsMap.RxDeduct

     INNER JOIN BenefitMapRiders AS SubAbuse 
        ON tempMapIDs.MapID = SubAbuse.MapID
           AND tempMapIDs.SubAbuse = SubAbuse.OldRiderOption
           AND SubAbuse.NewRiderOption = qryOptionsMap.SubAbuse

     INNER JOIN BenefitMapRiders AS Infertility 
        ON tempMapIDs.MapID = Infertility.MapID
           AND tempMapIDs.Infertility = Infertility.OldRiderOption
           AND Infertility.NewRiderOption = qryOptionsMap.Infertility

     INNER JOIN BenefitMapRiders AS Dental
        ON tempMapIDs.MapID = Dental.MapID
           AND tempMapIDs.Dental = Dental.OldRiderOption
           AND Dental.NewRiderOption = qryOptionsMap.Dental

     INNER JOIN BenefitMapRiders AS Chiro 
        ON tempMapIDs.MapID = Chiro.MapID
           AND tempMapIDs.Chiro = Chiro.OldRiderOption 
           AND Chiro.NewRiderOption = qryOptionsMap.Chiro     

     INNER JOIN BenefitMapRiders AS Vision 
        ON tempMapIDs.MapID = Vision.MapID
           AND tempMapIDs.Vision = Vision.OldRiderOption
           AND Vision.NewRiderOption = qryOptionsMap.Vision

     INNER JOIN BenefitMapRiders AS Drug 
        ON tempMapIDs.MapID = Drug.MapID
           AND tempMapIDs.Drug = Drug.OldRiderOption
           AND Drug.NewRiderOption = qryOptionsMap.Drug

Note that the above joins are not nested, thus the optimizer is free to evaluate them in any order it sees fit.

However, the above is not valid Access (ACE, Jet, whatever) syntax (sadly, it doesn't support the SQL Standard). Rather, it forces you to put each join within parentheses. Personally, I would keep the joins in the above structure and add the parentheses in a way that makes them as unobtrusive as possible e.g.

FROM (((((((
     tempMapIDs 

     INNER JOIN qryOptionsMap 
        ON tempMapIDs.NewDate = qryOptionsMap.RenewalDate
           AND tempMapIDs.NewPlanOption = qryOptionsMap.PlanOption
     )
     INNER JOIN BenefitMapRiders AS RxDeduct 
        ON tempMapIDs.MapID = RxDeduct.MapID
           AND tempMapIDs.RxDeduct = RxDeduct.OldRiderOption
           AND RxDeduct.NewRiderOption = qryOptionsMap.RxDeduct
     )
     INNER JOIN BenefitMapRiders AS SubAbuse 
        ON tempMapIDs.MapID = SubAbuse.MapID
           AND tempMapIDs.SubAbuse = SubAbuse.OldRiderOption
           AND SubAbuse.NewRiderOption = qryOptionsMap.SubAbuse
     )
     INNER JOIN BenefitMapRiders AS Infertility 
        ON tempMapIDs.MapID = Infertility.MapID
           AND tempMapIDs.Infertility = Infertility.OldRiderOption
           AND Infertility.NewRiderOption = qryOptionsMap.Infertility
     )
     INNER JOIN BenefitMapRiders AS Dental
        ON tempMapIDs.MapID = Dental.MapID
           AND tempMapIDs.Dental = Dental.OldRiderOption
           AND Dental.NewRiderOption = qryOptionsMap.Dental
     )
     INNER JOIN BenefitMapRiders AS Chiro 
        ON tempMapIDs.MapID = Chiro.MapID
           AND tempMapIDs.Chiro = Chiro.OldRiderOption 
           AND Chiro.NewRiderOption = qryOptionsMap.Chiro     
     )
     INNER JOIN BenefitMapRiders AS Vision 
        ON tempMapIDs.MapID = Vision.MapID
           AND tempMapIDs.Vision = Vision.OldRiderOption
           AND Vision.NewRiderOption = qryOptionsMap.Vision
     )
     INNER JOIN BenefitMapRiders AS Drug 
        ON tempMapIDs.MapID = Drug.MapID
           AND tempMapIDs.Drug = Drug.OldRiderOption
           AND Drug.NewRiderOption = qryOptionsMap.Drug

Note the above merely gives the appearance of nested joins but in reality the optimizer is still free to evaluate them in any order (this results in a loss of functionality -- can't specify an explicit order -- but that's Access for you!)

If the query is performing poorly, you should make sure that the fields used in the join are indexed.