Hello, reposting this since had no replies for a couple of days.
I'm trying to build advanced search page with a keyword textfield and a drop down menu to select from search by Category, Supplier or Trade Name very similar to the one here - www.caterer.com/products

I've got 3 tables - Suppliers(S), Products(P) and Categories(C) where in 'S' I've got SupID and all details, in 'C' CatID, CatName and Description and in 'P' ProdID, ProdName, and CatID and SupID which are linked to 'C' and 'S' accordingly.

Do I need to create new table or query in Access to combaine all the data?
If not, how to make menu show names from different tables? I tried creating three recordsets but that was not working either. Another thing - if I make menu static and assign values from recordsets, I need to pass those to results page together with a value from 'Keyword' text field. What the SQL query would be in that case?

This is driving me up the wall, tried all I could think of and the best I got was the first record from each table.

Help please
Thanks in advance

Can you explain exactly what you need a little more in detail? I think i can help you, but i don't understand exactly what you need.

cheers!

------------------------
Poolio

MSN:

www.eliziumdesign.com

Ohh, thank you, thank you!!

Right, here it goes - in a serarch page I have text field "Keyword" and drop down menu to choose from search by Products, Services or Brand Name. Now, there are 3 tables - Suppliers(S), Products(P) and Categories(C).

In 'C' I've got CatID, CatName (like Accountants, Air Cleaners, Caterer's Equpment, etc) and Description, in 'S' - SupplierID, CompanyName, Address, etc, and in 'P' - ProductID, ProductName, SuppplierID and CatID which are linked to 'C' and 'S'.

I need to set up the search in a way that user enters a keyword in a text field and selects from the 'search by Supplier, Service or Trade/Brand Name' Menu so that the search can be performed on all tables. Please have a look how it's done there -www.caterer.com/products.

If you need, I can upload the DB to my site where you can have a look at it.
Please help

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Can you explain exactly what you need a little more in detail? I think i can help you, but i don't understand exactly what you need.

cheers!

------------------------
Poolio

MSN:

www.eliziumdesign.com
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>