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!