Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Question Unanswered: SQL - Query (with innerjoin and left joins)

    Hi everybody i have a problem i have this sql query

    Select tblGroupDriver.Driver, tblGroupDriver.Archive From (
    SELECT DISTINCT table1.FKDriverID as Driver,table1.FKArchiv as Archive
    FROM (((table1 INNER JOIN table2 ON table2.FKSFPID = table1.FKSFPID)
    LEFT JOIN tbl3 ON tbl3.SFPID = table1.FKSFPID AND tbl3.SFPVersion = table1.FKSFPVersion)
    LEFT JOIN table4 ON tbl3.SFPID = table4.FKSFPID)
    LEFT JOIN tbl5 ON table4.FKGroupID = tbl5.GroupID
    WHERE table2.FKUserID = "121212" AND table2.Report = False AND tbl5.Name = "dsfaf" ) as tblGroupDriver WHERE ((tblGroupDriver.Driver) Like "V*")

    the problem is it seems to not work because of
    WHERE table2.FKUserID = "uid11196" AND table2.Report = False AND tbl5.Name = "dsfaf"
    if i dont add the last and like only use WHERE table2.FKUserID = "uid11196" AND table2.Report = False
    then everything works fine but if i have all 3 then access shuts down
    seems 3 is too much for it? does somebody know how i can solve that problem?
    Thanks a real lot
    Last edited by justme:-); 11-30-04 at 11:37.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I can't rememeber what the limit is in Access but you've not hit it with just 3 joins.

    not to confident about ".....as tblGroupDriver WHERE ((tblGroupDriver.Driver) Like "V*")" I think thats likely to be the fault not sure what you are trying to achieve here

    You may be having problems with a column named "name" - not sure but it doesn't help using reserved words for columns. There may be a conflict with the property "name"

  3. #3
    Join Date
    Oct 2004
    Posts
    105
    I can't rememeber what the limit is in Access but you've not hit it with just 3 joins.

    not to confident about ".....as tblGroupDriver WHERE ((tblGroupDriver.Driver) Like "V*")" I think thats likely to be the fault not sure what you are trying to achieve here

    You may be having problems with a column named "name" - not sure but it doesn't help using reserved words for columns. There may be a conflict with the property "name"

    hmm i have the line WHERE ((tblGroupDriver.Driver) Like "V*")" in another query too and it works well it also works in the query above as long as i dont set mor than 2 limits
    so i thought about doing something like () as table6 and then limit that table for the tbl5.Name but i dont really know how to do that in that query because of the left joins

  4. #4
    Join Date
    Nov 2004
    Posts
    8
    If you think you have used a reserved word (Like healdem suggests) you could try putting square braketts around the field name like:

    tbl5.[Name]

  5. #5
    Join Date
    Oct 2004
    Posts
    105
    I tried it with the [] but it no difference there

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm pretty certainthe fault is int he double use of where

    WHERE table2.FKUserID = "121212" AND table2.Report = False AND tbl5.Name = "dsfaf" ) as tblGroupDriver WHERE ((tblGroupDriver.Driver) Like "V*")

    I'd try

    WHERE table2.FKUserID = "121212" AND table2.Report = False AND tbl5.Name = "dsfaf" AND tblGroupDriver.Driver Like "V*")

    ***not tested***

  7. #7
    Join Date
    Oct 2004
    Posts
    105
    i think i need the where there because

    Select tblGroupDriver.Driver, tblGroupDriver.Archive From (

    from here
    SELECT DISTINCT table1.FKDriverID as Driver,table1.FKArchiv as Archive
    FROM (((table1 INNER JOIN table2 ON table2.FKSFPID = table1.FKSFPID)
    LEFT JOIN tbl3 ON tbl3.SFPID = table1.FKSFPID AND tbl3.SFPVersion = table1.FKSFPVersion)
    LEFT JOIN table4 ON tbl3.SFPID = table4.FKSFPID)
    LEFT JOIN tbl5 ON table4.FKGroupID = tbl5.GroupID
    WHERE table2.FKUserID = "121212" AND table2.Report = False AND tbl5.Name = "dsfaf" ) as tblGroupDriver

    to here its the new table tblGroupDiver with fields Driver and Arcive and then i only limit that entries by

    WHERE ((tblGroupDriver.Driver) Like "V*")

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK its a new one on me,
    So what is stopping you moving the like "V*" clause to the first select? Or failing that treat the extract as a seperate SQl clause and the like "V*" in a second query

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Right there is no need for the ((tblGroupDriver.Driver) Like "V*")
    to be part of the original select it can go in the subquery,
    written like this table1.FKDriverID Like "V*"
    That might help as you are no longer running an alias from the Subselect id also wrap the name in [] as this will help keep it distinct from any named constants
    id possibly do the same to the report
    but as access stores refrences to the queries id do as healdem suggests and write this as 2 queries

  10. #10
    Join Date
    Oct 2004
    Posts
    105
    It works now thank you all

Posting Permissions

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