I would like to make an Access database for my collection of scientific papers. From that database I should be able to find articles using descriptive keywords. The problem is I haven’t figured out how I could search for the articles needed using multiple keywords at the same time (eg. papers that fill the criteria: <keyword a> AND <keyword b> AND <keyword c>).
What kind of tables and queries I have to create to do that?
How about adding three fields to your table:
Keyword1, Keyword2 and Keyword3.
Then add your keywords in order of importance when you enter your paper into the database.
Create a search form where you can enter up to 3 keywords. In the criteria of your query under each of the Keyword fields tell it to search for the keyword on the form.
I have been using this system with great success. The only problem is you have to be consistent with your use of the keywords. I use a separate table with keywords and combo boxes on both the entry and the search forms for consistency.
Thanks Sum Guy,
I hadn't thought it that way. Using different keyword fields might really be a viable option in my case. However, I would prefer solution where I don't need to care about hierarchy of the keywords.
The problem is I haven’t figured out how I could search for the articles needed using multiple keywords at the same time (eg. papers that fill the criteria: <keyword a> AND <keyword b> AND <keyword c>).
Just curious, are you only going to allow up to 3 criteria?
Or are you trying to handle an unspecified number of criteria?
Am I correct in assuming that you have at least three tables; i.e. one for papers, one for keywords, and one for assigning multiple keywords to papers?
tblPapers
PaperID [PK]
strTitle
tblKeywords
KeywordID [PK]
strKeyword
tblPaperKeywords
PaperID [FK]
KeywordID [FK]
Are you also trying to search the test of the paper titles?
Yes, that’s correct. I have three tables just as you assumed, because based on what I have read elsewhere that’s thee solution. Initially I thought that using AND-operator would be obvious way to do the queries, but I haven’t figured out how to implement that kind of queries using the table design I have. I would really prefer a system where I can use unspecified number of keywords: sometimes I would use just two (or even one), sometimes four. It all depends how specific sort of article I’m searching for.
I would really prefer a system where I can use unspecified number of keywords: sometimes I would use just two (or even one), sometimes four. It all depends how specific sort of article I’m searching for.
If that's the case, just use the system that Evan gave you two posts ago. Either that, or look here for Jon K's QBF technique.
From what you say, ideally you would use this format if you have controls in which you are entering your search words:
Code:
WHERE (([field1] = forms!searchform!controlname1 OR
forms!searchform!controlname1 Is null) AND
([field2] = forms!searchform!controlname2 OR
forms!searchform!controlname2 Is null) AND
[B][U]
...etc, etc, for each control that can contain a search word in it)[/U][/B]
But, if you don't have controls on the form where you are inserting separate words into separate controls, and you just want to enter any number of words into one parameter search prompt, then use this:
Code:
WHERE [fieldOfYourChoice] = "*" & [parameterstring] & "*"
>>>>>Important things to note about wildcard searches with parameter strings<<<<<
Example I'm using: Criteria Field Value = "Hello World"
Parameter String = "orl"
1) If criteria is: "*" & [ParameterString] & "*", the search will return any record with the Criteria Field Value in it.
2) If criteria is: "*[ParameterString]*" , the search will not return any record with the Criteria Field Value in it, but it will return records with one-string field values that contain the sequence of characters "orl" in it. Example: If the field value was "World" instead of "Hello World", you would get that record returned.
Does this help you understand the process of wildcarding your searches better?
The ChangeQdefSQL() function is not required (you can create your own way to do it) but since I already had something that did the job, I went ahead and used it.
It would be nice to be able to search by author, year, journal and other criteria. I may want to build something like this for myself when I get some free time!
