Ms-access – Splitting 1 record on one table to become two records on another table

ms accessms-access-2010vba

I am REALLY very new to this so please excuse my ignorance, but I could really use some help!

I've got two tables: BillsFiled and Authors

On BillsFiled, there are the columns: ID, Symbol, Filedate, Type, Author, Title, Summary, among others

This is populated from an imported Excel file which is filled in by an outside source which cares not for my needs of referential integrity which means that the "Author" field can contain more than one entry, such as:

ID-Symbol-------Filedate------Type------------Author--------------------Title--------------------Summary

1  -   s001  ----  11/18/2014  --- Resolution -- Smith ; Stevens   -----     A Resolution to... ------- yadayada

2  -   h002   ---- 11/18/2014 ---  Bill   -----------     Diaz       --------------------            A Bill to...    -----------------    yadayada

3   -  s002  ----  11/18/2014  ---- Bill    ---------    Ryan ; Smith ; Harris--  A bill...    --------------------       yadayada

Because I need a record linking "Author" to "Symbol" I also have the table: Authors.
This just contains the columns: Symbol and Author.

It is my most fervent desire to run a query or some vba or a macro or something to split the multiple entries in "BillFiled" to append "Authors" such as: 

Symbol   -     Author

s001          Smith

s001          Stevens

h002          Diaz

s002          Ryan

s002          Smith

s002          Harris

I found a string of code from about a year ago offered on this site in response to a very similar question from another user and have tried using it, but am getting strange results. The code (updated with my table and column names) was:

Sub SplitIt()

Dim rstBillFiled As Recordset
Dim rstAuthors As Recordset
Dim Items() As String

' use dbOpenSnapshot to open the source table READ-ONLY
Set rstBillFiled = CurrentDb.OpenRecordset( _
        "SELECT Symbol, Author FROM BillFiled;" _
        , dbOpenSnapshot)
' use dbOpenDynaset to open the destination table READ-WRITE
Set rstAuthors = CurrentDb.OpenRecordset( _
        "SELECT Symbol, Author FROM BillFiled;" _
        , dbOpenDynaset)

With rstBillFiled
    ' .BOF is Beginning of the table
    ' .EOF is End of the table
    ' Checking if both are false means there are records in the
    ' source table
    If Not (.BOF And .EOF) Then
        ' get the first record from the source table
        .MoveFirst
        Do
            ' if Author is NULL (empty)
            If Nz(!Author, "") = "" Then
                    ' add a new record into the destination table
                    ' with data from the source table for Symbol
                    rstAuthors.AddNew
                    rstAuthors!Symbol = rstBillFiled!Symbol
                    ' set Author to NULL (empty)
                    rstAuthors!Author = Null
                    ' save the new record
                    rstAuthors.Update
            Else ' if Author IS NOT NULL
                ' convert Author into an array of strings
                Items = Split(Nz(!Author, ""), ";")
                ' loop through the array of strings
                For a = LBound(Items) To UBound(Items)
                    rstAuthors.AddNew
                    rstAuthors!Symbol = rstBillFiled!Symbol
                    ' Author is set to the current item in the array
                    rstAuthors!Author = Items(a)
                    rstAuthors.Update
                Next a
            End If
            ' load the next record from the source table
            .MoveNext
        ' repeat until the end of the source table is reached
        Loop Until .EOF
    End If
    ' close the source table
    rstBillFiled.Close
End With
' close the destination table
rstAuthors.Close

End Sub

With the instruction to then run SplitIt from the Immediate window.

When I do that, it does collect and split the records but all of them are added into "BillFiled" rather than "Authors"

Also it repeats the action with the first two records such that if there are 4 records in the source table attributable to 6 authors in total, it will add 8 new records to the source table and nothing to the destination table.

As I said in the beginning, I really don't know what I'm doing and don't have any experience with code, but am trying to build this database to help a family member with their business who doesn't have time to learn how to. I have some time but sadly no knowledge so if anyone out there could take pity on me and lend your experienced eye to this, I would be so grateful! Thanks!

Best Answer

See if this work.

 CREATE FUNCTION [dbo].[FN_CommaSeparator] (@array VARCHAR(max))

> RETURNS @Table TABLE (value VARCHAR(max))

AS
BEGIN
    DECLARE @separator_position INT
        ,@array_value VARCHAR(max)

    SET @array = @array + ','

    WHILE PATINDEX('%,%', @array) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @array)

        SELECT @array_value = LEFT(@array, @separator_position - 1)

        INSERT @Table
        VALUES (@array_value)

        SELECT @array = STUFF(@array, 1, @separator_position, '')
    END

    RETURN
END

CREATE TABLE #Temp (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

CREATE TABLE #Temp2 (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

INSERT INTO dbo.#Temp (
    Id
    ,Author
    )
VALUES (
    'S1'
    ,'ABC,BCD'
    )
    ,(
    'S2'
    ,'CDE,DEF'
    )
    ,(
    'S3'
    ,'EFG,FGH'
    )
    ,(
    'S4'
    ,'GHI,HIJ'
    )

DECLARE @Id VARCHAR(50)
    ,@Author VARCHAR(50)

DECLARE ABC CURSOR
FOR
SELECT Id
    ,Author
FROM dbo.#Temp t

OPEN ABC

FETCH NEXT
FROM ABC
INTO @Id
    ,@Author

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.#Temp2 (
        Id
        ,Author
        )
    SELECT @Id
        ,Value
    FROM dbo.FN_CommaSeparator(@Author)

    FETCH NEXT
    FROM ABC
    INTO @Id
        ,@Author
END

DEALLOCATE ABC

SELECT *
FROM dbo.#Temp2 t

DROP TABLE dbo.#Temp

DROP TABLE dbo.#Temp2
Related Question