X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Text Tags Stored In Single Field, Access 2016    
post Jan 9 2019, 09:01 PM

Posts: 6
Joined: 5-January 05

I have a database of products (books) and we use 'tags' to give additional sort/search terms on a Shopify site. we export our products out of access and into Shopify. most items have 1-4 tags. I'm pretty sure comma delimited words in a single field isn't the best practice because it makes it hard to sort and search and edit and I wasnt sure how to search on best practices for a field like that. If anyone has some wisdom or a thread on this type of thing, I'd appreciate it.
as an example something might have the tag field with: bestseller, graphic
lego, science
or no terms, or just a single term.
thanks and in advance and apologies for the vague request
Go to the top of the page
post Jan 9 2019, 09:45 PM

Posts: 3,042
Joined: 27-February 09

It would be more efficient to create a child table of keywords (ISBN, Keyword) maybe. Then you can index the keyword column and search it like a phone book. If the keywords are not split out, it's like searching a phone book for everyone with the first name of "Charles" (for example). You have to read the entire phone book because it's not sorted by first name, it's sorted by last name. Same problem with the design they're using.
Go to the top of the page
post Jan 10 2019, 07:23 AM

UA Admin
Posts: 34,970
Joined: 20-June 02
From: Newcastle, WA

Right. You should design your database according to best practices for relational databases, as noted.

Exporting that data into the format required by the application that consumes it is a different matter. There are several good examples here and elsewhere of VBA functions to concatenate strings from the records in the child table, including this one.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Jan 10 2019, 01:56 PM

Posts: 38
Joined: 15-January 18

If redoing your db as suggested is not an option you can try to use the native InStr function for your search. You could have a combo (or more) on a form to list your tags and use InStr([TagField],Me.cboSearchTag)>0 to identify the ones that have it. It is similar to using Like *TagWord* but I find it a bit faster.

Go to the top of the page
post Jan 11 2019, 09:14 AM

Posts: 6
Joined: 5-January 05

Thanks all. i did find a function to separate the words into separate fields and then built in 3 fields referencing a table of possible tags , the concatenated them for export. Being in the presense of common sense and hitchhiking off your ideas is just what I needed.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th May 2019 - 07:45 AM