Fog Creek Software
Discussion Board

SQL question

Hi all,
Sorry to ask such a basic and specific SQL question on this forum. I know that this forum is for general software related topics. But I don't belong to lot of forums online. It will be of great help if any of you able to help me.
I have an MS-Access table with some memo and text fields.
I have to design a query which searches these fields for some keywords. I have 10 keywords and I want the query to return records, if the record contains any 5 of those 10 keywords (if 50% of the keywords are present).
Is this possible to do in just MS-Access?
I know it will be possible if I am calling this table using some VB or Java or C++ program. I can pass one keyword at a time and then use some sort of count function and then display the records if the count is more than 5 or something.
But can i do something like this just in MS-Access or is there a better or simpler way to do this?
Thank you very much for all your inputs.

SQL Beginner
Sunday, January 12, 2003

You could do an access query that would work by listing (using OR) all the possible combinationds of five keywords but you'd sure have to like typing!

It shouldn't be too difficult to do in VBA (that is to say within Access) using a counter as you mentioned,but I'll leave the exact details to those with more experience.

Incidentally, these kinds of questions can be well answered on the ZDNet MSOffice forum (give my regards to Dan Vlas and Jethro UK if you do enqiuire there) and Woodys Office lounge

Slightly more advanced is the VISBAS beginners forum

Best of Luck

Stephen Jones
Sunday, January 12, 2003

SQL Beginner,

Access has a useful feature where you can call a VBA function from your sql statement.

So if you open a module and created a function called ItContainsWhatIWant that does all of the complex logic you then have your sql looks something like this:

Select * from table where ItContainsWhatIWant(col1, col2, col3) = true.

You'll have to pass your fields as paramaters.

This simplifies your sql code quite a bit, but does tie your database to Access.  You would have trouble if, for example, you decided to move to SQL server.

Still if you were moving to SQL Server, you could use the full text indexes anyway.

Performance wise, this is a lot slower than pure SQL but quicker than pure VBA.

Ged Byrne
Sunday, January 12, 2003

There is a zillion ms-access sites on the web.

I would use Usenet, and the ms-access hiricary from Microsoft is probably one of the busiest sections of their newsgroups.



Another good newsgroup for ms-access questons is

I will take crack at this:

My first question is how large of  file are we likely to work with? If the file is small, then just write the SQL. It will not run fast, but you don’t say how large the data file is.  If it is small, like a 1000 records, then just use a straight wild card search. No index will occur..but it will work.

Select bla,bla,bla where
(MyMemoField Like “*keyword1*”)  + _
(MyMemoField Like “*keyword2*”)  + _
(MyMemoField Like “*keyword3*”)  + _
(MyMemoField Like “*keyword5*”)  + _
(MyMemoField Like “*keyword6*”)  + _
(MyMemoField Like “*keyword7*”)  + _
(MyMemoField Like  “*keyword8*”)  + _
) <= -5

Each of the above conditions will evaluate to 0, or  -1 (true). Hence, 5 or more matches will result a value of = -5.

As mentioned, JET does NOT have a text indexing option, and thus the above will run very slow.

It is a code free solution. I would probably create a user defined function and use that in place of the above. I will say that one real cool feature of ms-access is that you can write and use VB functions in your sql. 

At risk of sounding a bit off, I wish sql-server used VB for it’s programming language in place of t-sql!!!

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Sunday, January 12, 2003

Thanks for all your inputs.
This a small MS-Access table and I need to use this query only one time. So Albert's simple solution worked perfectly fine.
Thank you Albert!

SQL Beginner
Sunday, January 12, 2003

Parse the text, fields and memo as the record is created and create an entry in a keywords table that contains the keyword and the unique ID of the field.  If you need to know which field in the record and where in the memo, then you can store the name of the field and the character position of the word as well.

Then for queries you search the keywords table, not the actual data.  You can use the results in a variety of ways,  order by unique id and then use 'count' would be one way.

The same thing works for free text retrieval.  I've several content management apps that use the same basic method, but they index all words, usually of five letters or more, shorter significant words are keywords. 

Simon Lucy
Monday, January 13, 2003

*  Recent Topics

*  Fog Creek Home