Results 1 to 6 of 6
  1. #1
    aflashman is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    3

    Convert single record into multiple records, parse memo field

    I am new to access but I have a feeling that my question will probably require VBA. If there is a simpler solution I would be interested to hear it.



    I have a table with two fields - Extract (which is a unique identifying number) and Narrative (which is in the memo format).
    Although within each narrative record is text, much of which should be in multiple records.
    The narrative goes like this

    [text string] text string text string etc [text string] text string text string ect [text string] text string text string ect
    (thus the memo always begins with a section enclosed in square brackets, but the square brackets occur again within the same record, but each time this occurs within the memo I want this to be the beginning of a new record).

    Thus I want it to go like this:

    [text string] text string text string etc
    [text string] text string text string etc
    [text string] text string text string etc

    Thus each text string is its own record, with the Extract ID number now duplicated for each new record.

    I have created my own clumsy workaround by exporting the narrative to Word and doing a find/replace where I substitute the left square bracket for paragraph break/left square bracket.
    Find: [
    Replace: ^p[

    This almost most works for me, but I was hoping there might be a way of doing this in Access.

    Any suggestions or pointers would be greatly appreciated.

    Stage 2 will be to then parse the start of the memo into its own field
    [text string]

    Thus I will now have three fields
    Extract, NewField, Narrative

    But I would happy to solve stage 1 first.
    Last edited by aflashman; 08-28-2011 at 09:05 PM.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    It is possible to do what you want.

    I do something similar in a Function.

    In brief:
    1. I have a query that I use as my data source.
    2. I read in one row of data at a time from the query.
    3. I parse the text & separate what I need [you would create a new line for each row that you want in your table].
    4. Write it to a TabDelimited .txt file.
    5. Import the .txt file into an Access Table.

    Do you think you can do this?

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Let me know if you need more help with this. It is only 'difficult' if you've never done it before!
    I'll be happy to help.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have to keep Narrative as a field? Once you have the text string you could write the new record to a table. I would think you need to know where the new record fits-- so I'd suggest some identifying number.
    In fact you may want to identify the newfield contents within the particular extract. For example:
    Extract001, line001, text
    Extract001, line002, text
    etc.

    You could use vba, then for each memo field put the memo data into a variable.
    Find the start and stop characters, append this record with its identifying number to a table, then delete that part of the variable content. Repeat parsing the variable looking for the start and stop characters, repeat until that memo text has been done.

    Read the next memo field into the variable, repeat the process until the you reach end of file.

    Air code for an approach... hope it's helpful.

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Here is sample code to do what you are wanting to do.
    I've had to hurriedly take out lines of code & rename variables etc - so this is not FUNCTIONING code.
    It should give you a good starting point for your code, though.

    Code:
     
    Function YourFunctionName()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL, strLine, strVariableName, strVariableName2 as String
    On Error GoTo Err_YourFunctionName
    Set db = CurrentDb
    strSQL = "Select * From [YourQueryname]"
    Close #1
    'Open the Text file for writing.
    Open "\\ServerName\DirectoryName\FileName.txt" For Output As #1
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
    With rs
     
    Do While Not rs.EOF
    'Get your field Values into a string variable - like this:
    strVariableName = rs!FieldNameInTable
    strVariableName2 = rs!NextFieldNameInTable
    'etc . . .
     
    'OR - just get everything into an strLine variable:
    'strLine will be what you write to the Text File.
    'This will be one row of data in your new Table.
    strLine = rs!FieldNameInTable
    strstrLine = strLine & vbTab & rs!NextFieldNameInTable
     
    'Write a Code Loop to parse the text in strVariableName 
    '& create a separate line of tab-delimited text.
     
    'Write the current Line to your text file 
    'and add a CarriageReturnLineFeed to create a New Line.
    Print #1, strLine & vbCr
    'Processing for current row that you create ends here.
     
    'Loop and create your next row from the CURRENT record of the recordset.
     
    'When you have finished splitting the current record into multiple rows:
    .MoveNext 'Move to next record in recordset.
    Loop 'Go Back to Do While to check if we are at the end of the file.
     
    End With
     
    'Close the Text file that now has each of your records split into multiple lines of Text.
    Close #1  
     
    Exit_YourFunctionName:
    If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
     
    Err_YourFunctionName:
    Resume Exit_YourFunctionName
    End Function
    When you're done with this, you will want to import the Text file into a Table in your database.
    Import | Delimited.
    If you are wanting to do this on a regular basis & want to automate it, you can put these steps into a Macro.
    If you name the Macro 'AutoExec', you can have your Windows Task Scheduler open your database at a set time.
    When the databse opens, AutoExec fires off and performs whatever you have in there.
    Let me know if you have more questions.

  6. #6
    aflashman is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    3
    Robeen and Orange thank you both for the tips.
    And Robeen a big thank you for showing me that code. I shall experiment with that on the weekend. I'll let you know if I can get it to work, but if not I might post back with a few more questions.

    In the meantime I have figured out how to do this in Excel thanks to another post in this forum, which with some small modifications works rather well.

    https://www.accessforums.net/program...rds-14253.html

    The Excel solution involves using the Text to Columns excel feature. I am not sure of the equivalent in Access, but I see Robeen your code has the line:

    Write a Code Loop to parse the text in strVariableName

    At this stage I don't know how to parse the text other than the Text to Columns feature in Excel.
    But I am sure with the help of Google I can figure this out. Hopefully this thread can stay open for a bit longer so I can post the final solution.

    Thanks very much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating multiple records from a single form
    By secretary in forum Forms
    Replies: 8
    Last Post: 07-18-2011, 04:03 PM
  2. Multiple records on a single page
    By neo651 in forum Forms
    Replies: 1
    Last Post: 06-29-2011, 10:21 PM
  3. Replies: 8
    Last Post: 01-21-2011, 10:28 AM
  4. Replies: 7
    Last Post: 11-13-2010, 08:08 AM
  5. Replies: 1
    Last Post: 12-10-2009, 08:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums