Hi. I have tried to simplify the issue but apologise in advance as it is somewhat complex. I have the code at the bottom as it might be self explanatory.
I am trying to manipulate a 3rd party DB that is part of a shopping site system. It has been poorly design and I cannot redesign it. There are 2 tables I need to manipulate:
Sections : This contains a list of all the sections on the website (1 row per section)
Products: This contains all the products in the website (1 row per product) as well something called fragments which are objects allow you to cross reference one section to another (again 1 row per fragment). Each row in the product table has a reference to the "Parent section" that this row belongs to. The fragments have an extra field that identifies the sections that belong to that fragment "Frag Section"
So as a practical example is:
- Section 1 has 2 fragments (frag A and frag B).
- Frag A refers to section 2 and frag b refers section 3
- Section 2 has 10 products (Prod 1 to 10)
- Section 3 has 5 products (Prod 11 to 15)
This means there are 3 rows in the section table and 17 rows in the product table. (in practice there are lots more products and sections).
I know the name of section 1 and I need to obtain the list of products that are referenced by it through the chain:
section 1 - frag A - Section 2 - Products 1 - 10, frag B - Section 3 - Products 11 - 15.
I would like to create a query to return all products (1-15). I am struggling because Access does not like me using the PRODUCT table twice (once to find the fragments with "Parent section" equal to section 1 and a second time to find the products which have the same "Product.nParentSectionID" as identified in the "Product.sLinkedItemID" field of the first query).
The SQL is:
Code:
SELECT Product.[Product Reference], Product.nParentSectionID FROM Product INNER JOIN
(SELECT Product.[Product Reference], Product.nParentSectionID, Product.sLinkedItemID
FROM [Catalog section] LEFT JOIN Product ON Product.nParentSectionID = [Catalog section].nSectionID
WHERE [Catalog section].[Section text] = 'SECTION 1' ) AS SECTIONSFOUND
on SECTIONSFOUND.sLinkedItemID = Product.nParentSectionID;
The problem is that Access complains about the join: SECTIONSFOUND.sLinkedItemID = Product.nParentSectionID. It says it is not allowed as it is a "memo OLE or hyperlink object"?
Any ideas on how to reform the sql would be gratefully received.
Many thanks
Tony