Queries criteria base on Table field

[denthui]

Please help me as I am new at MS Access so I dont know if I can do this and if can, how do I do it ?

I have two table: Orders and Category

Orders:


Ord # Descript Amount
1 Cake 10
2 Toner 30
3 Books 30
4 Tonners 10



Category:
Cat Text
Tonner like "*toner*" or like "*tonner*"



I want to creat a select query to select * from Orders where descript = category.text

I tried to put this in the query, however it become a parameter query.

I need it to return Orders # 2 and 4

Thank you for your time.

 

[namliam]

You cannot do it this way... You want to make an SQL more or less like so
select query to select * from Orders where descript like "*toner*" or like "*tonner*"

But you cannot do this using a table or anything... What is the total scope of what your trying to do...??
Have a search on the forum for "Search Form" (in the references forums) and see if that can help you along

 

[denthui]

Thanks for your reply.

I can hardcode the criteria, however I want to make it dynamic so users can later change the criteria and the search will varied.

 

[RossWindows]

You might find a solution by creating one more table.

tblOrders
tblCategories
tblSynonyms

In tblCategories you will have records with single words like "Tonner"
Then in tblSynonyms you will have records with single words like "Toner"

Then you will have to write a function that dynamically loops through each Synonym of the selected Category to build the SQL string on the fly.
So, in summary, yes it can be done but it will take a little work.

 

[denthui]

Thanks for your reply, however as I am just a beginer at this, I've no idea how write SQL or coding stuff. I'll stick with the hardcode.

 

[RossWindows]

Have a search on the forum for "Search Form" (in the references forums) and see if that can help you along

There are some sample databases that you can find with that search suggestion.
Download the samples and take a look at the code. I'm sure once you see how it's done, you'll have no problem... and we're always here to help!

 

[namliam]

Have a search on the forum for "Search Form" (in the references forums) and see if that can help you along

I know Ross already did this, but I want to re-itterate... try to use a search form, search the forum for samples and see if you can addapt it to suite your needs.

 

[denthui]

I've tried some examples for search form, however it did not match what I require.

thank you for your time.

p.s: do you know any good website where I can find tutorial for Access 2003? I cant seem to find any that explain things in simple term and step by step.

 

[namliam]

I can hardcode the criteria, however I want to make it dynamic so users can later change the criteria and the search will varied.

A search form will do this for you...

I.e. Your category table can be used...
Category:
Cat Text
Tonner like "*toner*" or like "*tonner*"

You have a "cat" Tonner, and a search of -like "*toner*" or like "*tonner*"-

Using a search form you display the categories Tonner and alike, hiding the "hardcore" like and what not parameters from view for the not so hardcore user.
Then using the search form you write/append the Like to the SQL of the query that is then executed.

Dont just think inside the limitations of the samples, the samples are a basic idea that you have to try and understand how it works, then take it make it part of yourself and make a new one to suite exactly your needs.

The #1 thing you need to "learn"/do is be flexible, think outside the box. Trust me, the search form is what you want and need.