How extract substrings of a column ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a query for a report
There is a column called Keywords which contains strings of keywords separated by semicolon
I need to produce a report counting records containing keyword
e.g.
row 1 keywords = "hardware;printer;ribbon
row 2 keywords = "hardware;monitor;screen

The report would need to print
hardware
printer
ribbon
monitor
screen

I'm having a hard time figuring out how to extract the keywords in SQL. I can only think of reading a recordset to extract them, but then how do I create the report ?
 
I need to create a query for a report.
There is a column called Keywords which contains strings of keywords separated by semicolons
Click to expand...

That's the source of the problem. You are embedding a many (item) to
many (keyword) relationship in a single field. Can you modify the
table design to have a table of Keywords, and a resolver table with
fields for the primary key of your main table and the keyword, so you
would have one *record* per keyword/item pair?
I need to produce a report counting records containing keywords
e.g.
row 1 keywords = "hardware;printer;ribbon"
row 2 keywords = "hardware;monitor;screen"

The report would need to print:
hardware 2
printer 1
ribbon 1
monitor 1
screen 1

I'm having a hard time figuring out how to extract the keywords in SQL. I can only think of reading a recordset to extract them, but then how do I create the report ?
Click to expand...

You can use the Split() function in VBA to extract all the keywords
into an Array - I'd suggest writing code to create the normalized many
side table suggested above, and use that table to generate the report.
In fact I can't think of a good way to do this otherwise!
 
Back
Top