Solving recursive query issue?

[tonygg]

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:

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

[ranman256]

no,you cannot query criteria on Memos,oles, hypers.
you CAN use the tProduct table twice, thrice in a query. A self linking table, I do this for some queries.

[CJ_London]

providing your memo field is less than 256 characters then you can use the cstr function to convert it to a string. e.g. assuming both these fields are memo fields

Product.nParentSectionID = [Catalog section].nSectionID

becomes

cstr(Product.nParentSectionID) = cstr([Catalog section].nSectionID)

Note you can only use these writing in sql, it will not be possible to show in the query window

Alternatively get the tables the way you want them, link to your web data file and run append queries rather than maketable queries

[tonygg]

Thank you both for taking a look. I think I misunderstood the issue. I thought it was about the multiple use of the product table rather than the particular field. I will try converting to a string and let you know. Many thanks Tony

[tonygg]

all sorted. thank you