Hi all... great forum!

I'm basically on a crash self-learning course in Coldfusion and database-driven sites, so I'm pretty sure I'm missing some basic concept that would make this an easy query. If anyone could point me in the right direction I'd really appreciate it.

This is for a site that deals in fruit juice products - extracts, concentrates etc. All I want to do is create a search form that queries an Access database for all product names containing the search term. For instance, a search on "apple" should return "apple juice concentrate", "apple extract" etc.

Now, that IS easy (even for me) when I try it on just one table. Problem is, I'm going to have multiple tables. I think I need to, because different types of products require different types of specs.

So, here's my "example" problem. The actual database will probably be a little more involved than this, but if I can make this work I'll be able to apply it to more tables, I assume:

1. We have a database with 3 tables - EXTRACTS, ESSENCES, CONCENTRATES. They all contain different columns, BUT they all contain a column PRODUCT_NAME. All the PRODUCT_NAME columns will be text data. None of their values will ever match each other completely.

2. I need a query that will search the PRODUCT_NAME columns in all three tables for records containing the term "apple". I assume this will involve a WHERE-LIKE statement.

I know I need some kind of join for this, and that's where it gets hazy. I haven't been able to find a reference that explicitly clarifies how a join would work in this case. All the examples I find seem to deal with a scenario where the values from one table will match those in another. None of my values will ever match across tables.

Also, I'm guessing this is gonna involve defining keys in the database. Another hazy part.

Thanks much for any help on this. Even a simple link to a reference that you feel would help would be really appreciated!

What you need is one table with a list of all of the products, and more fields for the different kinds of products to link to the original tables you have.