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:
Code:
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.