Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiple Join Criteria (A97)

    I have a number of queries created from the query grid that seem to have extraneous join conditions when I view them in SQL. As an example, I have the following query - it seems that the join conditions <font color=red>(Organizations.OrgID = Proposals.OrgID)</font color=red> and <font color=red>Proposals.ProposalName = Contracts.ProposalName</font color=red> are both repeated several times.

    Is this just 'one of those things' or does this affect performance. If the Jet engine is evaluating the same join criteria a number of times, does that slow down the query? Will I damage things beyond repair if I edit the SQL to take out the second-through-fourth repetitions of the same condition? Although I would likely catch a horrible crash (and revert to a back up - another hard-learned lesson) I am worried that I might miss a subtle flaw that I create this way.

    Finally, does anybody have any idea how this sort of thing starts and (if is is a performance issue) what to do to prevent it?


    SELECT Proposals.ResourceType, Proposals.TargetCOD, Proposals.ProposalName, Contracts.InitialPeriodDate, Organizations.Company, Proposals.RegionName, Proposals.Capacity, Proposals.Output, Proposals.EvalBy, People.PrimeContact, [People]![Salutation] & " " & [People]![Fname] & " " & [People]![Lname] & IIf(Not IsNull([People]![Hon]),", " & [People]![Hon]) AS Contact, People.Title, IIf(IsNull([People]![DirPhone]),[Organizations]![Phone],[People]![DirPhone]) AS PhoneNo
    FROM ((Organizations INNER JOIN Proposals ON (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID) AND (Organizations.OrgID = Proposals.OrgID)) INNER JOIN Contracts ON (Proposals.ProposalName = Contracts.ProposalName) AND (Proposals.ProposalName = Contracts.ProposalName)) INNER JOIN People ON Organizations.OrgID = People.OrgID
    WHERE (((Proposals.RFPSequence)=2) AND ((Contracts.EPADate) Is Not Null) AND ((People.PrimeContact)=Yes))
    ORDER BY Proposals.ResourceType, Proposals.TargetCOD, Proposals.ProposalName, Proposals.RegionName;


    Thanks for any help or suggestions

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multiple Join Criteria (A97)

    I have no idea why there are the extra join conditions.

    Tell me what does the design View look like?

    Are there multiple lines between Organizations and Proposals on OrgID?

    You should not create havic by deleting those extraneous joins.

    Save the query first as another name then try mucking about with the query.

    HTH
    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Join Criteria (A97)

    Check your relationships layout and see if you've somehow created multiple joins there. That would result in them turning up in queries as well.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Join Criteria (A97)

    Thanks Patt and Charlotte, for your suggestions. I have not had a chance to do much with them today, but will look at the relationships table tomorrow, and also just try hacking away at the SQL to see what the results are like.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •