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!