Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Problem with Left Join & VB6

    Hello, sorry but my English is not so good.

    I developed some appliction in VB6 using SQL server 2000 database but now I have to using the same application with MS access 2003/2007 and there is some problem with query.

    These are some of my tables:

    AccountInfo
    -AccountNumber*
    -CompanyID
    -Employee
    -OtherPerson
    .
    .
    .
    Employee
    -employeeID*
    -firstName
    -lastName
    .
    .
    .
    Company
    -companyID
    -address
    -phone
    .
    .
    .
    Month
    -monthID
    -month


    This is the query what is working using SQL server very well but MS Access is not supporting it.

    SELECT AccountInfo.Number, Company.CompanyName,Employee.name,Month.month
    FROM AccountInfo inner join Month ON monthID=accountInfo.monthID
    LEFT JOIN Company ON accountInfo.companyID=company.companyID
    LEFT JOIN Employee ON accountInfo.employeeID=employee.employeeID
    WHERE.........

    Problem is that for every Account record you have info about company OR employee and NOT both together because INNER JOIN results with NULL. Info about Month we have for any AccountInfo records so INNER JOIN is working OK.

    Is anyone have some idea how to do that ?
    Thanks.

  2. #2
    Join Date
    Mar 2010
    Posts
    1

    Problem with MS Access Left Join & VB6

    Hello, sorry but my English is not so good.

    I developed some appliction in VB6 using SQL server 2000 database but now I have to using the same application with MS access 2003/2007 and there is some problem with query.

    These are some of my tables:

    AccountInfo
    -AccountNumber*
    -CompanyID
    -Employee
    -OtherPerson
    .
    .
    .
    Employee
    -employeeID*
    -firstName
    -lastName
    .
    .
    .
    Company
    -companyID
    -address
    -phone
    .
    .
    .
    Month
    -monthID
    -month


    This is the query what is working using SQL server very well but MS Access is not supporting it.

    SELECT AccountInfo.Number, Company.CompanyName,Employee.name,Month.month
    FROM AccountInfo inner join Month ON monthID=accountInfo.monthID
    LEFT JOIN Company ON accountInfo.companyID=company.companyID
    LEFT JOIN Employee ON accountInfo.employeeID=employee.employeeID
    WHERE.........

    Problem is that for every Account record you have info about company OR employee and NOT both together because INNER JOIN results with NULL. Info about Month we have for any AccountInfo records so INNER JOIN is working OK.

    Is anyone have some idea how to do that ?
    Thanks.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in any query with more than two tables, msaccess requires parentheses around the joins, two at a time...
    Code:
    SELECT AccountInfo.Number
         , Company.CompanyName
         , Employee.name
         , Month.month
      FROM ((
           AccountInfo 
    INNER 
      JOIN Month 
        ON Month.monthID = AccountInfo.monthID
           )
    LEFT OUTER
      JOIN Company 
        ON Company.companyID = AccountInfo.companyID
           )
    LEFT OUTER
      JOIN Employee 
        ON Employee.employeeID = AccountInfo.employeeID
     WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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