July 29th, 2011, 07:16 AM
Two table query within query
I have two tables (Access DB), one with product info, other with manuals info like this:
Cat CDproduct Desc
1234 1001 Product 1001 belonging to Cat 1234
1234 1002 Product 1002 belonging to Cat 1234
5678 1010 Product 1010 belonging to Cat 5678
5678 9999 Product 9999 belonging to Cat 5678
Cat = Product Category, CDproduct = Product Code Indentifier
So in above table there is no manual for product 9999
I made a query to get a result matching "Cat", this works fine.
Now I want to use a single query on "CDproduct" comming from the original query "Cat"with a result that outputs Table one with a match on "Cat" and table 2 with a match on CDproduct, in other words, result should be products from table one, together with manuals from table two (if there is a manual found in table 2). Also products without manuals should be listed.
Any help in any direction is welcome.
July 29th, 2011, 09:14 AM
Please post the code/query you have now.
You should take a look at the Left Join syntax.
This will "include all of the records from the first (left - product) of two tables, even if there are no matching values for records in the second (right - manual) table."
July 29th, 2011, 02:39 PM
Thanks for directing me the right way. Figured it out with LEFT JOIN like you mentioned.
For those who want to see how, here is the solution which worked OK for me:
Two tables: All_Products and Documentation
All_Products table contains main category named MainCategory
ProdCatID comes from user input, this case a web page.
Combined JOIN LEFT with WHERE statement to filter out user input.
"SELECT All_Products.MainCategory, All_Products.ProductID ,Documentation.ProductID " & _
"FROM All_Products " & _
"LEFT JOIN Documentation " & _
"ON All_Products.ProductID=Documentation.ProductID " & _
"WHERE All_Products.MainCategory ="&ProdCatID&"
Again thanks for your help MrFujin!