Extracting contents from memo fields

[murgatroyd]

I have a table with a field that lists attributes (say,colours), like this.
[Colour]
Blue
Green
Red

I have another table with a key field ("Item") and two memo fields ("Back", "Front") that contain these attributes, like this.
[Item], [Back], [Front]


Item1, Red, Green
Item2, Blue, Green
Item3, Green, <null>
Item4, <null>, Blue; Red

The two memo fields can contain any number of attributes in any order. Multiple attributes within a field are separated by semicolons (like the "Blue;Red" in the "Front" field in the "Item4" record above).

I want to create a report that lists each of the attributes and which records contain them.

I wondered whether it could be done something like this.

(1) Make a query to generate a list that has one row for each attribute within each memo field, like this.

Item1, Back, Red
Item1, Front, Green
Item2, Back, Blue
Item2, Front, Green
Item3, Back, Green
Item4, Front, Blue
Item4, Front, Red

(2) Make another query to sort the first query by attribute/memo/item, like this.

Blue, Back, Item2
Blue, Front, Item4
Green, Back, Item3
Green, Front, Item1
Green, Front, Item2
Red, Back, Item1
Red, Front, Item4

(3) Make a report to group the second query by attribute/memo/item, like this

BLUE
Back
- Item2
Front
- Item4

GREEN
Back
- Item3
Front
- Item1
- Item2

RED
Back
- Item1
Front
- Item4

However, I don't know how to do the first query, listing the contents of the memo fields in separate rows. I realize this would be easier if the data were not in memo fields (actually varchar fields in a linked SQL table); however, I cannot change the design and am hoping someone can advise on extracting the contents of the memo fields (which are consistently delimited by semicolons) into separate rows in a query.

I wondered whether the "Split" function could help with this ...
https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

... but I don't know whether or how it could be used in a query and am hoping someone can advise on this.

[orange]

Based on your description, my guess is that you should be using tables for some things and not memo fields.
It would be better for you and readers if you would tell us WHAT you are trying to do in simple plain English.
What is the database about?

I recommend that you work through this tutorial to get a clear understanding of database design concepts. It will take about 45 minutes to 1 hour, but you will learn, and what you learn can be applied to any database. You have to work through the tutorial to get the benefit.

Good luck.

[murgatroyd]

Thanks for your reply.

In simple plain English, I want to make a query to convert data in this format (where "Back" and "Front" are memo fields containing any number of substrings, delimited by semicolons) ...

[Item], [Back], [Front]
Item1, Red, Green
Item2, Blue, Green
Item3, Green, <null>
Item4, <null>, Blue; Red

... into this format.

Item1, Back, Red
Item1, Front, Green
Item2, Back, Blue
Item2, Front, Green
Item3, Back, Green
Item4, Front, Blue
Item4, Front, Red

I realize this would be easier if the data were not in memo fields (actually varchar fields in a linked SQL table); however, changing the database design is not an option, hence I am hoping someone can advise on extracting the contents of the memo fields (which are consistently delimited by semicolons) into separate rows in a query.

[Micron]

IMHO, you'd need a function that can parse the items (using the ; as a delimiter) and write them to a DAO recordset then write them to a table which you then query.

[murgatroyd]

Thanks for your reply. I'm afraid I don't know enough to follow the suggestion without further guidance.

[Micron]

If you provide a zipped sample db in a 2007 version (do a save as) I will see what I can do. You can limit the tables to only those required, or the data itself or do whatever you feel is necessary to protect whatever information you think needs protecting. If I don't see simpler way, this will take a bit of work so my position is I'm willing to explore it further as long as I don't have to start from scratch. For any dialog that doesn't benefit others in terms of insight or solution to the problem, PM me.

[murgatroyd]

Thanks for your further reply and kind offer. I have some experience with fairly simple queries and reports in an existing system, which uses linked tables from an SQL database; however, I don't have any experience with creating a new self-contained test database but will get back to you if/when I manage to do this.