[Access 2003] Searching within multi-value fields
Meuhlize
Posted messages
3
Status
Member
-
tessel75 -
tessel75 -
Hello!
I have recently started working with Access, and I am creating a database of texts. I want to create a "keywords" field to facilitate searches within this database. Is it possible to put several independent keywords in the same field? And when I perform a keyword search, will the software search among these words individually?
For example:
Article 1 - keywords: A, B, C
Article 2 - keywords: B, E
It should be that if I search for the keyword "A", the software gives me article 1, and if I search for B, it gives me articles 1 and 2.
Thank you!
Configuration: Windows XP / Firefox 15.0.1
I have recently started working with Access, and I am creating a database of texts. I want to create a "keywords" field to facilitate searches within this database. Is it possible to put several independent keywords in the same field? And when I perform a keyword search, will the software search among these words individually?
For example:
Article 1 - keywords: A, B, C
Article 2 - keywords: B, E
It should be that if I search for the keyword "A", the software gives me article 1, and if I search for B, it gives me articles 1 and 2.
Thank you!
Configuration: Windows XP / Firefox 15.0.1
9 answers
Hello,
Thank you for your quick response. I have taken your explanations into account, but I think I may not have explained myself clearly. I will elaborate a bit.
I have a KEYWORD table that has 2 fields: CODEMOT (primary key) and KEYWORD (which allows me to populate my list with keywords that have not yet been defined).
And a DOCUMENT table with fields like Title, Date... and Keyword. The latter is related to the primary key of the KEYWORD table and has a data type of: combo box.
I also have an input form to enter the documents. I would like to select multiple keywords using a Keyword control linked to my keyword field in the document table. I want the selected keywords in my form to be saved in the field (logically, that’s the purpose of a form). However, my form only offers a list of the few keywords I entered for testing, and I can only select one.
This poses a problem since a book can have multiple keywords.
I don’t know if this is very clear; I did my best. If you cannot answer, I will try to decipher the forum in the Access archives.
Thank you again for responding so quickly and with clear explanations (especially for the last part).
And thank you in advance.
Thank you for your quick response. I have taken your explanations into account, but I think I may not have explained myself clearly. I will elaborate a bit.
I have a KEYWORD table that has 2 fields: CODEMOT (primary key) and KEYWORD (which allows me to populate my list with keywords that have not yet been defined).
And a DOCUMENT table with fields like Title, Date... and Keyword. The latter is related to the primary key of the KEYWORD table and has a data type of: combo box.
I also have an input form to enter the documents. I would like to select multiple keywords using a Keyword control linked to my keyword field in the document table. I want the selected keywords in my form to be saved in the field (logically, that’s the purpose of a form). However, my form only offers a list of the few keywords I entered for testing, and I can only select one.
This poses a problem since a book can have multiple keywords.
I don’t know if this is very clear; I did my best. If you cannot answer, I will try to decipher the forum in the Access archives.
Thank you again for responding so quickly and with clear explanations (especially for the last part).
And thank you in advance.
Good evening,
Besides the fact of putting several keywords in the same field, which is a bit strange, but why not, the search must be based on a query whose selection criterion will be of the form: " Like "*A*B*C*" " (like "Like" in VBA), (Attention to the quotes).
"*" is the wildcard character in Access, so it must be placed systematically before and after the searched keyword to ensure that if it is in the middle of a list, it will be retrieved properly.
Furthermore, if you want to retrieve multiple keywords at the same time, either distinct or combined, you will have to juggle with the "And" and "Or"
See the tutorial on wildcard characters in Access.
Good luck!
Besides the fact of putting several keywords in the same field, which is a bit strange, but why not, the search must be based on a query whose selection criterion will be of the form: " Like "*A*B*C*" " (like "Like" in VBA), (Attention to the quotes).
"*" is the wildcard character in Access, so it must be placed systematically before and after the searched keyword to ensure that if it is in the middle of a list, it will be retrieved properly.
Furthermore, if you want to retrieve multiple keywords at the same time, either distinct or combined, you will have to juggle with the "And" and "Or"
See the tutorial on wildcard characters in Access.
Good luck!
Hello,
First of all, thank you for your help and I apologize for my late response; I've had some internet issues.
I managed to do as you explained with the *.
However, I am stuck again: the final goal of my work is an online library accessible to everyone. In the keyword search query, I have used the following formula:
Criteria : Like [What keywords]
Is it possible to program so that the asterisks are added automatically (so that they don't have to be typed every time)?
I tried Like *[What keywords]*, with " and ', but nothing works.
Thank you!
First of all, thank you for your help and I apologize for my late response; I've had some internet issues.
I managed to do as you explained with the *.
However, I am stuck again: the final goal of my work is an online library accessible to everyone. In the keyword search query, I have used the following formula:
Criteria : Like [What keywords]
Is it possible to program so that the asterisks are added automatically (so that they don't have to be typed every time)?
I tried Like *[What keywords]*, with " and ', but nothing works.
Thank you!
Hello,
I am also creating a database in Access 2003 and your question seems interesting to address mine.
I can't seem to select multiple keywords for a record during data entry.
For example: Document 1 has keywords A, B, and D. Document 2 has B, C, and D.
I don't know how to enable multiple selections.
Thank you in advance.
I am also creating a database in Access 2003 and your question seems interesting to address mine.
I can't seem to select multiple keywords for a record during data entry.
For example: Document 1 has keywords A, B, and D. Document 2 has B, C, and D.
I don't know how to enable multiple selections.
Thank you in advance.
Good evening,
First of all, congratulations for taking the effort to search for an answer that approaches what you are looking for. It's so rare that it deserves to be pointed out.
In order to respond fairly correctly to your question, it is necessary to know how your main document is structured, here your table with its fields, and possibly the form that presents the records from your table.
The question then arises as to whether your keywords appear in the same field or in different fields, and in what order. To illustrate:
The Three Musketeers:
Keyword1 = three, musketeers,
Keyword2 = novel, France
Keyword2 = Cape, sword
or
Keyword1 = novel, France ,
Keyword2 = Cape, sword
Keyword2 = three, musketeers
or even a single word for each of the keywords.
As this type of search is often done through a form where keywords are entered through controls provided for that purpose, you will either need as many input controls as you have fields where the searched words may be found, or build your query in such a way that all fields (or THE field) are queried at the same time. In any case, you need to plan a query that searches for the correct word in the correct field. It's up to you to be imaginative to cover all possible cases, both in the search and in how the keyword fields will be filled by users.
For the rest, as I mentioned earlier and as Meuhlize also found, you will need to juggle with the "and" and the "or" and also the "&" (which are concatenation like "+")
Best of luck.
First of all, congratulations for taking the effort to search for an answer that approaches what you are looking for. It's so rare that it deserves to be pointed out.
In order to respond fairly correctly to your question, it is necessary to know how your main document is structured, here your table with its fields, and possibly the form that presents the records from your table.
The question then arises as to whether your keywords appear in the same field or in different fields, and in what order. To illustrate:
The Three Musketeers:
Keyword1 = three, musketeers,
Keyword2 = novel, France
Keyword2 = Cape, sword
or
Keyword1 = novel, France ,
Keyword2 = Cape, sword
Keyword2 = three, musketeers
or even a single word for each of the keywords.
As this type of search is often done through a form where keywords are entered through controls provided for that purpose, you will either need as many input controls as you have fields where the searched words may be found, or build your query in such a way that all fields (or THE field) are queried at the same time. In any case, you need to plan a query that searches for the correct word in the correct field. It's up to you to be imaginative to cover all possible cases, both in the search and in how the keyword fields will be filled by users.
For the rest, as I mentioned earlier and as Meuhlize also found, you will need to juggle with the "and" and the "or" and also the "&" (which are concatenation like "+")
Best of luck.
Good evening,
Thank you for taking a look at my response.
To be honest, since I took my first steps with Access using version 2 and then 98 (I have the 2010 version today, don't worry), I didn't bother to learn about tables with drop-down list data types. I learned to manage without them; it's enough to have adjacent tables.
That said, I still find it a bit strange to have a separate table for your keywords; it seems simpler to have in the same "DOCUMENT" table fields like Keyword1, Keyword2, Keyword3, etc. Because with your two tables, the question arises as to how your one-to-many relationships will be organized.
You respond: “a DOCUMENT table with fields like Title, Date... and Keyword. The latter being related to the primary key of the KEYWORD table.” This implies that the "one" side is on the "Keyword" table, which is odd because what happens when you need multiple keywords for the same document, which happens almost all the time. But I'll set that question aside for now, and I will address it later if you wish.
To return to the issue of selecting to retrieve the desired document by selecting a keyword, the difficulty is indeed the one I mentioned, knowing how and in what order they were entered. And this is where you have to juggle with "*", "AND", and "OR".
The solution can't be simple, so I would advise having multiple controls in your search form, 4 or 5, where you can enter the desired keywords, the source query for the drop-down lists being the same for the 4 or 5, but the selected word being different, of course. And for your actual search query, the criteria would be of the type: "Like 'Keyword1' or like 'Keyword2' or etc."
Finally, it is normal for your drop-down list to only display the words you entered as examples since those are the only ones in your "Keywords" list. Likewise, it is normal that you can only select one since each corresponds to one record; you cannot expect to select multiple records at the same time with a drop-down list.
Hoping I have been somewhat clear. I have done my best too.
Thank you for taking a look at my response.
To be honest, since I took my first steps with Access using version 2 and then 98 (I have the 2010 version today, don't worry), I didn't bother to learn about tables with drop-down list data types. I learned to manage without them; it's enough to have adjacent tables.
That said, I still find it a bit strange to have a separate table for your keywords; it seems simpler to have in the same "DOCUMENT" table fields like Keyword1, Keyword2, Keyword3, etc. Because with your two tables, the question arises as to how your one-to-many relationships will be organized.
You respond: “a DOCUMENT table with fields like Title, Date... and Keyword. The latter being related to the primary key of the KEYWORD table.” This implies that the "one" side is on the "Keyword" table, which is odd because what happens when you need multiple keywords for the same document, which happens almost all the time. But I'll set that question aside for now, and I will address it later if you wish.
To return to the issue of selecting to retrieve the desired document by selecting a keyword, the difficulty is indeed the one I mentioned, knowing how and in what order they were entered. And this is where you have to juggle with "*", "AND", and "OR".
The solution can't be simple, so I would advise having multiple controls in your search form, 4 or 5, where you can enter the desired keywords, the source query for the drop-down lists being the same for the 4 or 5, but the selected word being different, of course. And for your actual search query, the criteria would be of the type: "Like 'Keyword1' or like 'Keyword2' or etc."
Finally, it is normal for your drop-down list to only display the words you entered as examples since those are the only ones in your "Keywords" list. Likewise, it is normal that you can only select one since each corresponds to one record; you cannot expect to select multiple records at the same time with a drop-down list.
Hoping I have been somewhat clear. I have done my best too.