Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Query to analyze contents of memo fields

    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 whether it is possible (and if so how) to do the first query, listing the contents of the memo fields in separate rows?
    Last edited by Murgatroyd; 2016-03-26 at 20:01.

  2. #2
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    65
    Thanks
    0
    Thanked 8 Times in 8 Posts
    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 <Item1, Back, Red> and <Item1, Front, Green>.

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

    Dave

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,164
    Thanks
    19
    Thanked 99 Times in 88 Posts
    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.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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/lib...(v=vs.90).aspx
    Last edited by Murgatroyd; 2016-03-26 at 20:02.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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
    That should get you going hopefully.
    Last edited by patt; 2016-03-26 at 21:40.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    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)?

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,164
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by Murgatroyd View Post
    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?
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Memo field manipulation

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

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by gsmith-plm View Post
    ... 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: <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).
    Last edited by Murgatroyd; 2016-03-29 at 07:01.

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by patt View Post
    Here is the database, hope this is what you want.
    Thanks kindly. I will check it out and let you know how it goes.

  14. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,164
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by Murgatroyd View Post
    "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.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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 & "*"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •