Splitting a field into multiple fields (author names of a publication)

[lowhum]

Hello,
I'm trying to create a publications database in MS Access and I have a following problem:
Publications can have from one to many authors. From the exported information from an external database (Web of Science),
all authors are exported in one field, separated by a ";". An example would be "Nocheva, H; Sabit, Z; Bakalov, D; Grigorov, E". to be split into -> "Nocheva, H", "Sabit, Z", "Bakalov, D", "Grigorov, E".

1. How can I split the individual authors into separate fields?
2. How can I create an authors table without duplicating values? I can do that manually by analyzing the data, but it will be extremely time consuming (some publications can have more than 100 authors). Is there a way to do this automatically?
3. After splitting the authors field into separate fields, how can I organize the database so they still relate to the publication?



Any help would be greatly appreciated!

[June7]

Actually, the authors should be separated into records, not fields, in a dependent table. This will require VBA to split the data and save each to a record. Procedure would open a recordset, loop recordset, use string manipulation functions, write records.

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[lowhum]

Thanks for the reply. Yes, the authors should be separated into records in a related table.
I have very little experience with db, so thanks for the clarification.
Here is the db attached. Basically, it has just one big table with publication data and a document type table I did.
What VBA code should I use to split the authors?
What code can be used for the other columns for example for the "Source title" column to remove duplicate values and create dependent tables?
Thanks in advance!

[June7]

Really should remove 2021 from table name.

Need another table: PubAuthors.
With at least two fields:
Author (text type)
PubID (long integer number type)

Code in a general module:

Sub SplitAuthors()
Dim rsSrc As DAO.Recordset, rsDest As DAO.Recordset, db As DAO.Database, x As Integer, y As Integer
Set rsSrc = CurrentDb.OpenRecordset("SELECT ID, Authors FROM Pubs")
Set db = CurrentDb
Set rsDest = db.OpenRecordset("SELECT * FROM PubAuthors")
Do While Not rsSrc.EOF
    y = UBound(Split(rsSrc!Authors, ";"))
    For x = 0 To y
        rsDest.AddNew
        rsDest!PubID = rsSrc!ID
        rsDest!Author = Split(Replace(rsSrc!Authors, "; ", ";"), ";")(x)
        rsDest.Update
    Next
    rsSrc.MoveNext
Loop
End Sub

Then should really create a table of Authors and replace the author name with AuthorID in PubAuthors. That's another multi-step process.

When all is done, delete Authors from Pubs table.

[davegri]

Deleted as obsolete.

See next post

[davegri]

On further reflection, I determined the pubs/author configuration needs to be Many-to-Many. Attached is revised DB that will create the new tblAuthors, new tblPubAuthorJunc and insert all necessary data and linkages.
Also included are two reports to demonstrate the power of the MTM configuration.

lowhum-pubs_2021_test-davegri-v02.zip

[lowhum]

Thanks a lot, #June7 and #davegri for your help!
Yes it is a many-to-many relation btw authors and publications.
I will try it out and tell you where it got me.