Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts

    Access SQL need to JOIN another table - require help

    Below is my SQL, Basically I have another table I need to join into the SQL The table name is: tblProductCategories
    the columns are: ID, ProductID, CategoryID, ProductOrder
    I know that its supposed to be something like:
    INNER JOIN tblProductCategories ON tblProducts.ID=tblProductCategories.ProductID

    Code:
    rs.source = "SELECT tblProducts.RelatedProductID1, tblProducts.RelatedProductID2, tblProducts.RelatedProductID3, " &_ 
    			"tblProducts.Col_Manufact, tblProducts.Col_Brief, tblProducts.Image1, tblProducts.Image2, tblProducts.Image3, tblProducts.Image4, tblProducts.Image5, tblProducts.Thumb1, " &_
    			"tblProducts.Col_Model, tblProducts.Col_Desc, tblProducts.ProductID, tblProducts.ID, Col_ProdName, " &_ 
    			"tblProducts.Col_Price, tblCollections.CName " &_ 
    			"FROM tblCollections RIGHT JOIN tblProducts ON tblCollections.ID = tblProducts.Col_Manufact " &_
    			"WHERE tblProducts.ID=" & qsID & "AND (Season=" & intActiveSeason & " OR Season=4)"

    this is what I have tried
    Code:
    rs.source = "SELECT tblProducts.RelatedProductID1, tblProducts.RelatedProductID2, tblProducts.RelatedProductID3, " &_ 
    			"tblProducts.Col_Manufact, tblProducts.Col_Brief, tblProducts.Image1, tblProducts.Image2, tblProducts.Image3, tblProducts.Image4, tblProducts.Image5, tblProducts.Thumb1, " &_
    			"tblProducts.Col_Model, tblProducts.Col_Desc, tblProducts.ProductID, tblProducts.ID, Col_ProdName, " &_ 
    			"tblProducts.Col_Price, tblCollections.CName, tblProductCategories.* " &_ 
    			"FROM tblCollections RIGHT JOIN tblProducts ON tblCollections.ID = tblProducts.Col_Manufact " &_
    			"INNER JOIN tblProductCategories ON tblProducts.ID=tblProductCategories.ProductID" & _
    			"WHERE tblProducts.ID=" & qsID & "AND (Season=" & intActiveSeason & " OR Season=4) ORDER BY tblProductCategories.ProductOrder Asc"

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Access is fussy. It *insist* that you use parens around joins, something like this:
    Code:
    SELECT ...
    FROM ( A INNER JOIN B ON A.field = B.field )
    LEFT JOIN C ON C.field2 = B.field2
    So I would *strongly* suggest that you change your RIGHT JOIN into a LEFT JOIN, for simplicity and follow the above pattern.

    Code:
    SELECT ...
    FROM ( tblProducts INNER JOIN tblProductCategories ON tblProducts.ID=tblProductCategories.ProductID)
    LEFT JOIN tblCollections ON tblCollections.ID = tblProducts.Col_Manufact
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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