There isn't an elegant way to handle this. With a properly normalized structure what you're asking for is really the hardest thing to do (to me). There's the rub, you have the correct structure but what you want to get to makes a non-normalized structure a better alternative but then your tables will be carrying a lot of blank records and would make other searches harder so this is what I propose:
Create a table called 'tblAuthorStrings', have the Document unique identifier as your primary key (just set the field to number) and a field called 'authorstring'
create a query called qryDocAuthorProcessing based on your junction table linking documents to authors, include the author table, add the first and last name fields from the authors table, sort the table the document identifier FIRST, followed by last name of the author (ascending order) and first name of the author (ascending order)
Next run a query like this either as part of your regular maintenance, or every time you update the table itself
Code:
Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim iPrevDoc As Long
Dim iCurrDoc As Long
Dim sAuthorString As String
Set db = CurrentDb
db.Execute ("DELETE * FROM tblAuthorStrings")
Set rst = db.OpenRecordset("qryDocAuthorProcessing")
iPrevDoc = rst!doc_id
Do While rst.EOF <> True
iCurrDoc = rst!doc_id
If iCurrDoc <> iPrevDoc Then
db.Execute ("INSERT INTO tblAuthorStrings (DocID, AuthorString) VALUES (" & iPrevDoc & ", '" & Left(sAuthorString, Len(sAuthorString) - 2) & "')")
iPrevDoc = iCurrDoc
sAuthorString = rst!authorfn & " " & rst!authorln & ", "
Else
sAuthorString = sAuthorString & rst!authorfn & " " & rst!authorln & ", "
End If
rst.MoveNext
Loop
db.Execute ("INSERT INTO tblAuthorStrings (DocID, AuthorString) VALUES (" & iCurrDoc & ", '" & Left(sAuthorString, Len(sAuthorString) - 2) & "')")
Set db = Nothing
This will populate tblAuthorStrings with the document identifier and one long string with the author names. use this table to retrieve your author names for query/reporting purposes NOT
Windows 8 
Access 2007
Reply With Quote
