Adding Tags to MS-Access database

Question

EDIT: I guess I wasn't as clear with this question as I had intended. I know how to search for multiple keywords, what I don't know how to do is assign multiple keywords to the same item:

I am making a database of journal articles, with the intent that people can search through it via keywords. While it is fairly easy to let people search for multiple keywords (gathering all articles that have this term AND that term) I have not found/seen a way to assign multiple keywords per article. As a simple example:

item 1 is "shiny"

item 2 is "blue"

item 3 is "shiny" AND "blue"

item 4 is "round" AND "shiny"

I would like the user to be able to search for "shiny" and have items 1,3,4 come up or search for "blue" and have items 2,3 come up etc.

@David W. Fenton mentions a "N:N join table". I have no idea what that is but he describe exactly what I want to do: "Storing multiple attributes in a single field" as if it is impossible, I would have thought that would be a not uncommon usage.

Solution 1

I know this is lame, but this does what I wanted: all i had to do was select one of the keywords then click Home > Selection > Contains and only the documents with that term were shown. I'm sure this isn't the proper way but it does everything I need.

Solution 2

The common usage is to encapsulate the text with a character such that BOTH SIDES of the word have the mark, and a blank entry has merely one of the encapsulators. You append new keywords by adding the keyword and the mark at the end.

The problem is that the encapsulating mark must not occur in the data, so it can not be a typical letter or punctuation mark to be reliable. You are also potentially limited by the size of the data field.

The CORRECT usage is to create a new table perhaps called KEYWORDS, with fields ID, journalID, keyword. The journalIDfield would be the ID of the particular journal articla. ID would be a unique auto-number field. Keyword would be a single keyword.

You could then do a search using a JOIN.

Comments

[dylan murphy]

hrm. maybe i wasn't as clear as i was hoping. some articles, will need to have multiple keywords. i am still rather unclear as to how to implement what you said, my first journal article is about dredging, spawning, and migration, so i tried to put dredgingspawningmigration in the Keywords field but it still just sees that as being one large word.

[henry700]

One problem is that * is a wildcard for searches. What I was saying is that in your implementation, you'd want perhaps ,dredging,spawning,migration, and your search criterion might be *,dredging,* which means (anything before)(one comma)dredging(one comma)(anything after). The problem with any scheme you pick, though is that the delimiter (a comma in my example) cannot be data because if it is, then the algorithm will believe you have multiple keywords split at the delimiter.

[dylan murphy]

can it not just search for keywords in one field only? what about the ~ squiggley thing, tilde i think. is that reserved?

[dylan murphy]

oh ok, well i wasn't sure because the only thing in that field is individual words with no punctuation so there is no risk of the delimiter being data. so now in the keywords field for my first entry is ~dredging~spawing~migration~ . when i click on Pivot table and drag my title field into the big details box and drag my keywords field in the filter box, i want to be able to pick key words - 1 or more - and see the articles that contain them. sorry if i wasnt explaining this well.

[henry700]

The reason behind putting delimiters is that some smaller keywords might be contained within larger keywords, and so you include the delimiters to prevent cat from matching against catheter.. You now want to filter the data. If you have natural language options, you are looking for contains or similar wording. contains ~dredging~ or ~spawning~. In Access SQL query language it would be like '*~dredging~*' or like '*~spawning~*'

[henry700]

You would use AND if you want the articles to match multiple keywords.

[henry700]

I would personally use an SQL query by the way: select * from journal_articles where (keyword like '*~dredging~*' and keyword like '*~spawning~*') or keyword like '*~migration~*' would select articles where they are about BOTH (dredging and spawning) OR migration only. (see: office.microsoft.com/en-us/access-help/… )

[dylan murphy]

i appreciate you trying to help me, but this is supposed to be something my boss and coworkers can open and with a few clicks see the journals that have the keywords they want. they are not going to learn how to use sql for something they use occasionally when just doing ctrl+f and typing the keyword in takes a handful of seconds.

[David W. Fenton]

This answer confuses me. The CORRECT answer is a N:N join table. Storing multiple attributes in a single field is denormalized and leads to all kinds of problems. The paragraph beginning "The CORRECT usage" seems to begin describing a N:N structure, but is quite incomplete. Any other implementation is really going to be a huge mistake.

[henry700]

It may be incomplete by description but one must take into account the OP's admission "but I've not used Access before" when introducing a new concept.

[ytk]

then it IS the proper way.