Linking items from table to other items in the same table

[James]

I created a table that will allow the user to assign similar items to the
currently viewed item. It works almost as planned.

For example: If a user adds a record that says"Item A" is similar to "Item
B", "B" will be displayed in lstSimilarItems on "A's" record.

However, I can't seem to get it to work the other way (If "A" is similar to
"B", "B" is also similar to "A"). Is there a way to make "A" show on "B" AND
"B" show on "A" without having to add it twice???

Any help will be greatly appreciated. MY current SQL is listed below-->

SQL:

SELECT tblSimilarItems.SimilarItemsID, tblProduct.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblSimilarItems INNER JOIN tblProduct ON
tblSimilarItems.SimilarProductID = tblProduct.ProductID
WHERE (((tblSimilarItems.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;

 

[John W. Vinson]

For example: If a user adds a record that says"Item A" is similar to "Item
B", "B" will be displayed in lstSimilarItems on "A's" record.

However, I can't seem to get it to work the other way (If "A" is similar to
"B", "B" is also similar to "A"). Is there a way to make "A" show on "B" AND
"B" show on "A" without having to add it twice???

A UNION query joining two queries going "both ways":

SELECT tblSimilarItems.SimilarItemsID, tblProduct.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblSimilarItems INNER JOIN tblProduct ON
tblSimilarItems.SimilarProductID = tblProduct.ProductID
WHERE (((tblSimilarItems.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;
UNION ALL
SELECT tblSimilarItems.SimilarItemsID, tblSimilarItems.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblProduct INNER JOIN tblSimilarItems ON
tblProduct.ProductID = tblSimilarItems.SimilarProductID
WHERE (((tblProduct.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;

 

[James]

Thanks for your help John.

I am getting an improper use of brackets error, so I got rid of them and it
works sort of. In the 2nd select statement you have
tblSimilarItems.ProductName, but there is no field named that in the table
tblSimilarItems. Since SimilarProductID and ProductID both use table
tblProduct for the product name what should I do?

For example: If a user adds a record that says"Item A" is similar to "Item
B", "B" will be displayed in lstSimilarItems on "A's" record.

However, I can't seem to get it to work the other way (If "A" is similar to
"B", "B" is also similar to "A"). Is there a way to make "A" show on "B" AND
"B" show on "A" without having to add it twice???

A UNION query joining two queries going "both ways":

SELECT tblSimilarItems.SimilarItemsID, tblProduct.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblSimilarItems INNER JOIN tblProduct ON
tblSimilarItems.SimilarProductID = tblProduct.ProductID
WHERE (((tblSimilarItems.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;
UNION ALL
SELECT tblSimilarItems.SimilarItemsID, tblSimilarItems.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblProduct INNER JOIN tblSimilarItems ON
tblProduct.ProductID = tblSimilarItems.SimilarProductID
WHERE (((tblProduct.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;

 

[James]

Never mind! I crated a query that linked the product name to the correct ID
and it works as planned. Thanks again!

For example: If a user adds a record that says"Item A" is similar to "Item
B", "B" will be displayed in lstSimilarItems on "A's" record.

However, I can't seem to get it to work the other way (If "A" is similar to
"B", "B" is also similar to "A"). Is there a way to make "A" show on "B" AND
"B" show on "A" without having to add it twice???

A UNION query joining two queries going "both ways":

SELECT tblSimilarItems.SimilarItemsID, tblProduct.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblSimilarItems INNER JOIN tblProduct ON
tblSimilarItems.SimilarProductID = tblProduct.ProductID
WHERE (((tblSimilarItems.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;
UNION ALL
SELECT tblSimilarItems.SimilarItemsID, tblSimilarItems.ProductName AS [Similar
Product], tblSimilarItems.SimilarProductID
FROM tblProduct INNER JOIN tblSimilarItems ON
tblProduct.ProductID = tblSimilarItems.SimilarProductID
WHERE (((tblProduct.ProductID)=[Forms].[frmProduct].[txtProductID]))
ORDER BY tblProduct.ProductName;