[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Add Searchable Tags to a Database Record

Posted on 2008-11-17
Medium Priority
Last Modified: 2013-11-29
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?
Question by:AaronGreene1906
  • 3
LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 22982518
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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22982815

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)

LVL 85
ID: 22983545
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22986061
"it can be agile enough, if your Purchase Order is large enough"
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22986746
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.



Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question