Query to analyze contents of memo fields

Topic

New Reply

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,
Item4, , 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 whether it is possible (and if so how) to do the first query, listing the contents of the memo fields in separate rows?

Reply | Quote

Replies

Hi Murgatroyd,
Having to parse data that’s fetched from the Item table could have been avoided if the Item table had the same columns as the desired result of your Step 1, that is, if it consisted of the fields Item, Memo, and Color. For example, two of the rows would have been and .

Was there a reason for the Item table’s having its actual layout?

Dave

Reply | Quote

I don’t want to come off like the data police but…

Memo fields are notes, not data. Data belongs in discrete fields with a specific purpose. You really need to re-look at this design.

Reply | Quote

Thanks for the replies. 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.
https://msdn.microsoft.com/en-us/library/6x627e5f(v=vs.90).aspx

Reply | Quote

Thanks for the replies. 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.

There is no simple answer for this because memo fields, by their very nature, lack structure and order (organization). But something you said has me wondering what the structure really is.

You mention that the memo field is “actually varchar fields in a linked SQL table”. Could you clarify that some? Also, how is the data entered? Is it manually entered as a string: Item, Back, Front?

Reply | Quote

… memo fields, by their very nature, lack structure and order (organization)…. You mention that the memo field is “actually varchar fields in a linked SQL table”. Could you clarify that some? Also, how is the data entered? Is it manually entered as a string: Item, Back, Front?

The database is part of a large business application. Reports are done using Access 2010 as a front end connected to the SQL Server 2008 R2 database as a back end via an ODBC link. The memo fields appear as type “varchar” in SQL Server but as type “memo” in Access.

“Item”, “Back” and “Front” are separate fields, manually entered using an Access form. 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:
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).

Reply | Quote

”Item”, “Back” and “Front” are separate fields, manually entered using an Access form. 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.

I haven’t really worked with SQL since SQL92 was the defacto standard so I’m only thinking out loud here. I’m wondering if the data goes in as normalized data but is assembled into an easily displayed/reported memo field. Is it possible that the data still exists separately as a related table with discreet values?

I’m afraid that I’m too much of a Codd/Date/Pascal/Celko relational theory purist/curmudgeon. So, I should probably leave well enough alone and let other more current folks work on this one.

Reply | Quote

I wouldn’t bother with queries trying to solve this, I would use VBA to build a table (say tblFound) that has 3 fields viz:
Item
FrontBack
Colour
nb. where FrontBack is a text field that hold either the word Back or Front. Item is the same as the field described above, and Colour holds the colour found.
A VBA example:
[Code]
dim dbs as DAO.Database, rs as DAO.Recordset, rsC as DAO.Recordset, rsF as DAO.Recordset
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset(“MainTable”)
Set rsF = dbs.OpenRecordset(“tblFound”)
Do While Not rs.EOF
dim iPos as Integer
Set rsC = dbs.OpenRecordset(“Colours”)
Do Until rsC.EOF
iPos = InStr(1, rs!Back, rsC.Colour, vbBinaryCompare)
if iPos > 0 then
rsF.Addnew
rsF!Item = rs!Item
rsF!BackFront = “Back”
rsF!Colour = rsC!Colour
rsF.Update
End If
rsC.MoveNext
Loop
‘ do same for front
Set rsC = dbs.OpenRecordset(“Colours”)
Do Until rsC.EOF
iPos = InStr(1, rs!Front, rsC.Colour, vbBinaryCompare)
if iPos > 0 then
rsF.Addnew
rsF!Item = rs!Item
rsF!BackFront = “Front”
rsF!Colour = rsC!Colour
rsF.Update
End If
rsC.MoveNext
Loop
rs.MoveNext
Loop
‘ cleanup
rs.Close
Set rs = Nothing
rsC.Close
Set rsC = Nothing
rsF.Close
Set rsF = Nothing
dbs.Close
Set dbs = nothing
[/CODE]

That should get you going hopefully.

Reply | Quote

Thanks for your reply. I’m afraid I don’t know enough to use this without further guidance. Would I put the code into a query somewhere, and would it generate a temporary table whose fields could then be used in the same query, or would it create an actual table (note that this is a linked SQL database) that could then be used with a regular query?

Reply | Quote

I would attach this to a button on a form.
Behind the button you would put this code.
You would have to change the names of the tables to your table names.
Create a table named tblFound with field names:
Item
BackFront
Colour
This table can sit on your access frontend and used as a linked table in a query.

Reply | Quote

Thanks for your further reply. I’m afraid my knowledge of Access is fairly modest, and I’m not quite following how this would work.

Is it (1) a self-contained utility procedure that would read through the memo fields in my original table (which is a linked table in an SQL database back end) and extract the contents of the memo fields into separate rows in a new table, so I would first run this procedure to create the new table and then run a separate query to analyse the new table (and therefore I would need to re-run the procedure to refresh or re-create the new table every time the contents of the original table were changed)?

Or is it (2) a part of a query that would automatically extract the contents of the memo fields from the original table into a new table (so the query could then analyse the new table) whenever the query was run?

In either case, would the new table be permanent (remain when Access is closed) or temporary (disappear when Access is closed)?

Reply | Quote

It is (1) that is code behind a button on a form. I would create the table once, and leave it in the Access database.

The code I provided above needs a line to delete all the rows in that table, viz:
CurrentDb.Execute “DELETE * FROM tblFound”

Then you could run this form by pressing the button and the table will be ready for the query you will create.

If I get time tomorrow I will make a small database that does this.

What version of Access are you running, I will develop this in Access 2007.

Reply | Quote

Here is the database, hope this is what you want.

Reply | Quote

Here is the database, hope this is what you want.

Thanks kindly. I will check it out and let you know how it goes.

Reply | Quote

I think query #1 can be done with this (you could even modify it to append the records to a table for further manipulation):

SELECT Item, “Back” AS Position, Colour FROM tblItems, tblColours WHERE Back LIKE “*” & Colour & “*”
UNION SELECT Item, “Front”, Colour FROM tblItems, tblColours WHERE Front LIKE “*” & Colour & “*”

Reply | Quote

“This part of the application is actually to do with selecting personnel for various tasks requiring various skills.”

A consequence of this is that if any skill is a substring of at least one other skill, as in “typing” and “light typing”, then the elegant (in my opinion) solution suggested by MarkLiquorman might sometimes fetch skills that aren’t germane.

Dave

Reply | Quote

David,
Thanks for the compliment, and you made a valid point about a skill also being a substring of another skill. However, I think that can be handled. Here is my original SQL and a revised version (and we are back to talking about colours):

SELECT Item, “Back” AS Position, Colour FROM tblItems, tblColours WHERE Back LIKE “*” & Colour & “*”
UNION SELECT Item, “Front”, Colour FROM tblItems, tblColours WHERE Front LIKE “*” & Colour & “*”

SELECT Item, “Back” AS Position, Colour FROM tblItems, tblColours WHERE “;” & Back & “;” LIKE “*;” & Colour & “;*”
UNION SELECT Item, “Front”, Colour FROM tblItems, tblColours WHERE “;” & Front & “;” LIKE “*;” & Colour & “;*”

What this does is put a semi-colon in front of the string being searched, then also putting it around the Colour. So when searching for the Colour “Red”, the query looks for “;Red;” such that it would not find a match in “;Light Red;”.

Reply | Quote

Hi Mark,
I was wondering whether your revised SQL accounts for a space between a semicolon and the colour following it.

Dave

(Edited to remove a bunch of stupid stuff I wrote.)

Reply | Quote

I just looked back at the original post, and there was apparently a leading space before each color. So yes, my WHERE clause would have to be adjusted accordingly. All of which assumes, of course, that the real data is consistent!

Reply | Quote

I just looked back at the original post, and there was apparently a leading space before each color. So yes, my WHERE clause would have to be adjusted accordingly. All of which assumes, of course, that the real data is consistent!

Thanks for your replies. The data are consistent. The contents of the memo fields are delimited by a semicolon (plus a space, as you note); e.g., “Red; Green & blue; Yellow”.

However, I think there may have been some misunderstanding; I don’t know enough to know exactly what your code will do, but you mention “searching” (possibly because I mentioned “this part of the application is to do with selecting personnel for various tasks requiring various skills”?). I meant that this table is used for that function, but that is not what I want to do here (the application already does that very well). I don’t want to *extract* just *parts* of the contents that match something else. I want to *list* the *entire* contents of these memo fields but split into substrings, according to the semicolon delimiters.

Reply | Quote

Your original post said 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

Well, that’s exactly what my query does! So what is the misunderstanding?

Reply | Quote

Your original post said this:

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

Well, that’s exactly what my query does! So what is the misunderstanding?

Your code refers to two tables (tblItems, tblColours), and you mention “searching for a colour” and “finding a match”. Does this mean the code reads through a colours table and lists which records in the items table have each of those colours in the memo fields?

I don’t have a table for the colours, just one table for the items. The colours are just typed into the memo fields in the items table manually, delimited with semicolons. I don’t want to *extract* just *parts* of the contents of the memo fields that match colours from another table. I want to *list* the *entire* contents of the memo fields but split into substrings, according to the semicolon delimiters.

Example

Input:
[Item], [Back], [Front]
Item1, Red, null
Item2, null, Blue
Item3, Green, Whatever is here; Red

Output:
Blue, Front, Item2
Green, Back, Item3
Red, Back, Item1
Red, Front, Item3
Whatever is here, Front, Item3

Reply | Quote

What you will have to do is to write a function to separate the fields you want by doing a search for ; (taking into account the end condition where there is no ; ) and write these to a new table which would be linked to the main table.
If you want further help let us know.

Reply | Quote

Thanks for your further reply.

I found a solution with help from another source, similar to your suggestion.

Code:

Private Sub Run_Click()
Dim spl_String() As String, x As Integer, rst As DAO.Recordset

  CurrentDb.Execute “Delete * From ResultTable”
  Set rst = CurrentDb.OpenRecordset(“dbo_Item”, dbOpenDynaset, dbSeeChanges)
  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 ResultTable ( Item, Where, Colour ) ” _
            & “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 ResultTable ( Item, Where, Colour ) ” _
            & “Values (‘” & rst![Item] & “‘ , ‘Front’, ‘” & LTrim(spl_String(x)) & “‘)”
          Next x
        End If
      End If
      rst.MoveNext
    Loop Until rst.EOF

End Sub

Reply | Quote

Glad you got it solved.

Reply | Quote