Add Searchable Tags to a Database Record

I have been asked to develop a database of general contractors and their specialties.  I have a certain set of specialties that the majority of contractors will fall into, but I'd like to add the ability for users to add tags to each company.  I would also like the ability to include these tags in queries/filters.  Is this possible using MS-Access as a front end to a SQL Server back end?
Aaron GreeneProgrammerAsked:
Who is Participating?
 
rockiroadsCommented:
You could create another table which holds the tags

ID, ContractorID, Tag

ID = unique id to this table (autonumber for example)
ContractorID = ID to the contractor record
Tag = your keyword

one keyword, one record

So now this is a one-many relationship with your contractors table.

You can now query based on this table to find contractors


0
 
Jeffrey CoachmanMIS LiasonCommented:
AaronGreene1906,

Just some notes on these "Free Form" tags:

The issue with these "Free Form" tags is that very often they end up not being very consistient.
Thus yeilding inaccurate/unpredictable/inconsistient query results.

Someone searches for "Wood Worker", while it is input as "WoodWorker"
Is it "Drywall" or "SheetRock", is it spelled with two words or one?
Should the search for "Payroll" or "Salary"

How will you limit the data stored in these "Tags"?
Supose someone starts putting in personal notes: "Does not show up on time" "Smells like cigarettes"
(How would you search data like this?)

Lastly you must consider How you will construct these queries.
("AND" Logic, "OR" logic, combinations of AND and OR, Contains, Starts with, Ends with, ...ect)

JeffCoachman
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I have a very similar situation as Jeff describes with my main client.

They always want ways to "tag" items, so they can (supposedly) search for them more quickly when needed. I implemented something like rocki suggested, and get fairly regular complaints that "I searched for a tag of 'DN001', which I know exists, and it doesn't find anything". Of course, a quick check shows the tag entered as "DN 001" or "DN-001", which then elicits the response "the system should be agile enough to find this" ... to which I respond "it can be agile enough, if your Purchase Order is large enough" and that pretty much ends the exchange.

IOW, be careful of implementing features like this, and make sure your clients understand the limitations that go along with free-form text entry.
0
 
Jeffrey CoachmanMIS LiasonCommented:
"it can be agile enough, if your Purchase Order is large enough"
LOL
;-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
To All,

Just so we are clear.

I am in no way saying that rockiroads' post should not be used.

On the contrary, what rockiroads has proposed, is the standard way of doing things like this, and as such, qualifies as a valid solution.

I was just alerting you to some of the issues you may run into by using "Free Text"

An enhancement might be to create a list, (However long), of specific *One Word* keyword search terms.
And create a dropdown in conjunction with rockiroads' post.

So you can use this dropdown to add the multiple search terms for each company.
This way you have some measure of control over what is entered, and ultimately searched for.

;-)

JeffCoachman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.