Return a data from a table that doesn't have a relationship

[Guest]

Hello,

I have a table of products, and each product can have a category, for
example, "laptop" or "desktop".

I have another table that has a top-level category name (i.e "Computers")
and a memo field that stores sub categories ("laptop, desktop" etc). There is
no relationship between these two tables.

Is it possible to create a query that will list each product, and the
top-level category that it belongs to, i.e the top-level category, that has a
sub-category which contains the products' category?

I can't make many structural changes because I didn't design this database.

Cheers,

 

[Guest]

Small update - it wasn't a memo field, just a text field with several lines
of text in it.

 

[David Cox]

This is untested but might work. Create a category field and join on it.
Enter in the design grid:-

Category:iif(instr([yourtextfield],[yourcategory]},[yourcategory],"")
The idea is if Access finds "Laptop" in the text field it will create the
temporay field [Category] with the value "Laptop", and, hopefully join on
it.

 

[John Spencer]

You should be able to use an non-equi join to do this. The SQL would
look something like the following. You can't build this type of join
using the query grid although you could start there.

SELECT Products.*, Categories.Category
FROM Categories INNER JOIN Products
ON Categories.Subcategory LIKE "*" & Products.Category & "*"

To do this in the query grid
-- add both tables
-- Drag from Categories.Subcategory to Products.Category
-- select your fields to be displayed
-- Open the Query in SQL view (View: SQL on the menu)
-- Find the on clause and edit it to look like the on clause above.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================