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, but I don't know whether or how it could be used in a query and am hoping someone can advise on this.

 

[arnelgp]

it would be easy to extract the data you need if you revised your second table:

fields:
ItemName, Attribute(short text), Value(short text)

one attribute record for each item.
if possible, create another table for standard attributes that can be present in your second table. that way all items lookup this table for the attributes.

 

[JHB]

...
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, but I don't know whether or how it could be used in a query and am hoping someone can advise on this.

It is impossible to do what you require in a query, you need some VBA code to split the content of the memo field and put it into a table, one record for each part of the memo field, like you show it under (1).

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

You can use the Split function for doing that.

 

[murgatroyd]

it would be easy to extract the data you need if you revised your second table ...

Thanks for your reply, but "I realize this would be easier if the data were not in memo fields ... however, I cannot change the design."

 

[murgatroyd]

It is impossible to do what you require in a query, you need some VBA code to split the content of the memo field and put it into a table, one record for each part of the memo field, like you show it under (1).

You can use the Split function for doing that.

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

 

[JHB]

I'm afraid I don't know enough to that without further guidance.

Okay, I've made an example for you in the attached database.
Open/View the report.

 

[murgatroyd]

Thanks kindly. However, the link says "SplitMemo.accdb", but when I click on it to open/save it, the name is "attachment.php".

 

[arnelgp]

use firefox or chrome browser.

 

[The_Doc_Man]

Arnelgp suggested that you have a design flaw in the table. Your response was:

however, I cannot change the design.

Then you cannot directly solve the problem. You cannot query contents of a memo field without SOME non-standard help. MEMO fields impose constraints because they don't play well with SQL and string inspection functions. The design you have is not consistent with the goal you state (because of the table format issue).

JHB suggested using VBA and the SPLIT function. Your response was

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

If this is a class assignment - and this smells pretty much like one - then we don't usually do your homework for you. If this is NOT homework, then why are you not allowed to manipulate table structure? Can you at least create temporary tables that will cease to exist when you are done with them?

The correct way to do this is to write a subroutine to use SPLIT on each Memo field to then build a separate child table (for which the parent is the table containing the memo field and the key field) listing the attributes individually. Then SQL and the report wizards can do the rest. If your problem is over-constrained to disallow this kind of solution, you are stuck.

 

[murgatroyd]

use firefox or chrome browser.

Thanks, that solved the problem.

 

[murgatroyd]

If this is a class assignment - and this smells pretty much like one - then we don't usually do your homework for you. If this is NOT homework, then why are you not allowed to manipulate table structure? Can you at least create temporary tables that will cease to exist when you are done with them?

Thanks for your reply.

This is not a class assignment. I have some experience with fairly basic queries and reports but am not familiar with more advanced concepts.

Changing the design is not an option because the database is part of a large business application, where these memo fields are required to allow contents of varying number and length, which are consistently delimited by semicolons.

Reporting is done outside the application using Access as a front end on the linked SQL database as a back end, and I want to create a report to analyse the contents of these memo fields.

Creating temporary tables should not be a problem. (Would they appear in the Access front end or the SQL back end?)

The correct way to do this is to write a subroutine to use SPLIT on each Memo field to then build a separate child table (for which the parent is the table containing the memo field and the key field) listing the attributes individually. Then SQL and the report wizards can do the rest.

This approach sounds promising, and I would be pleased to know more.

 

[jdraw]

Can you tell us more about the business --just an overview?

I find this confusing/troubling for a viable business.

the database is part of a large business application, where these memo fields are required to allow contents of varying number and length, which are consistently delimited by semicolons.

Perhaps you could provide readers with a few samples of your memo fields.

 

[JHB]

..
This approach sounds promising, and I would be pleased to know more.

Did you try the sample database I posted?

 

[murgatroyd]

Can you tell us more about the business --just an overview?

I find this confusing/troubling for a viable business.

Perhaps you could provide readers with a few samples of your memo fields.

The business is a successful provider of services in the publishing industry.

The application works very well.

The contents of the "Back" and "Front" memo fields can be any length but are carefully and consistently structured, with semicolon delimiters, and they are not free-form but are restricted to a specified set of options.

Examples of memo fields:
0 items: <null>
1 item: "Red"
4 items: "Blue; Green; Orange, pink & purple striped; Yellow"

I have used fictional examples to keep things clear and simple (hopefully). This part of the application is actually to do with selecting personnel for various tasks requiring various skills. The report I want to create is to list which personnel ("Item") possess which levels of skill ("Front", "Back") for each task (colour).

 

[murgatroyd]

Did you try the sample database I posted?

I have now been able to check out the example database that you kindly prepared. Your help is much appreciated. However, I'm afraid my knowledge in this area is not very advanced, and I can't quite follow how your design works.

(a) Your MainTable is similar to the main table in my application, and I can follow that.

(b) Your SplittedItem table and report look like what I am trying to achieve.

However, I can only see these items and can't see how (b) is generated from (a). Thanks again for any further guidance you may be able to add.

 

[JHB]

..
However, I can only see these items and can't see how (b) is generated from (a). Thanks again for any further guidance you may be able to add.

Below is the code in the Report Open event, which insert the data in the table "SplittedItem"

Private Sub Report_Open(Cancel As Integer)
  Dim spl_String() As String, x As Integer, rst As DAO.Recordset
  
  CurrentDb.Execute "Delete * From SplittedItem"
  Set rst = CurrentDb.OpenRecordset("ItemTable")
  If Not rst.EOF Then
    Do
      If Not IsNull(rst![Back]) Then
        spl_String = Split(rst![Back], ";")
        For x = 0 To UBound(spl_String)
          CurrentDb.Execute "INSERT INTO SplittedItem ( Item, IsWhere, TheColor ) " _
          & "Values ('" & rst![Item] & "' , 'Back', '" & LTrim(spl_String(x)) & "')"
        Next x
      End If
      If Not IsNull(rst![Front]) Then
        spl_String = Split(rst![Front], ";")
        For x = 0 To UBound(spl_String)
          CurrentDb.Execute "INSERT INTO SplittedItem ( Item, IsWhere, TheColor ) " _
          & "Values ('" & rst![Item] & "' , 'Front', '" & LTrim(spl_String(x)) & "')"
        Next x
      End If
      rst.MoveNext
    Loop Until rst.EOF
  End If
End Sub

 

[murgatroyd]

Below is the code in the Report Open event, which insert the data in the table "SplittedItem"

OK I see now, thanks.

I gather the code first clears the output table each time it is run - is that correct?

Does the code create the output table when it is first run, or does the table need to be created separately somehow?

Could the code be run just from a button on a form, so the output table could then be used with separate queries and reports?

My Access 2010 front end is a .mdb file (not .accdb), and my input table (dbo_Item) is ODBC linked from a SQL Server back end. Does that require anything different?

 

[JHB]

.
I gather the code first clears the output table each time it is run - is that correct?

Correct

Does the code create the output table when it is first run, or does the table need to be created separately somehow?

I think you can answer that yourself!
Then why clear the output table if a new table is created.

Could the code be run just from a button on a form, so the output table could then be used with separate queries and reports?

Yes

My Access 2010 front end is a .mdb file (not .accdb), and my input table (dbo_Item) is ODBC linked from a SQL Server back end. Does that require anything different?

Try it - post back if you expired any problem, then we'll take it from there.

 

[murgatroyd]

"Does the code create the output table when it is first run, or does the table need to be created separately somehow?"

I think you can answer that yourself!
Then why clear the output table if a new table is created.

How is the table created when the code is first run?

 

[JHB]

"Does the code create the output table when it is first run, or does the table need to be created separately somehow?"
How is the table created when the code is first run?

The table is created manually.