Help? Master list of terms link to individual records
I've been trying to figure out if there is a calculation or function that can do what I am trying to do and I haven't had much luck. Here is what I'm trying to do:
I have a table containing records of artwork. Each artwork record has one field containing a list of subject terms. I want a related table that would be a master list of all the subject terms from all the artwork records. This could be either one field containing a list of all the terms, or each record could be its own subject term (I'm not sure which way would suit my needs better, or if it would even matter). I do want the master list of terms to be in ABC order. I want the Master List of subject terms to update automatically when a new subject term is added to the list of subject terms for each of the artwork records in the related table. I also want the subject term field for my artwork records to fill in with terms from the master list - if I started typing the beginning of a word, it would fill in the rest of the word using the terms from my master list.
Does this make sense? Is this possible? Any tips would be appreciated!