Seems to me you should have three tables instead of two (I'd do the same in Access also...).
That way, you have exactly one record in authors for each different author that can be assigned via the Article_Author join table to any number of different articles and any given article can be linked to any number of different article records...
Question, does it truly matter who is listed 2nd in the list? Since you need to find each author by searching your alphabetized list, the first name in the list is critical, but seems to me which name is second or third is not.
If so, Adding an extra table occurrence to link in other contributing authors would seem to do the trick here. To implement the above relationship, you'd have key fields like these:
Articles::ArticleID = Article_Author::ArticleID
Authors::AuthorID = Article_Author::AuthorID
Making a second table occurrence of Article_Author, Et_Al and linking it like this:
Article_Author::ArticleID = Et_Al::ArticleID AND
Article_Author::AuthorID ≠ Et_Al::AuthorID
You could define these calcuations in Article_Author to generate your index entries:
IndexEntry: AuthorName & ", " & Substitute ( List (Et_Al::AuthorName) ; ¶ ; ", " )
Now you can do a find of all Article_Author records for a given ArticleID, sort the records by IndexEntry and export the records to get your index entries.
PS. To make the 2nd table occurrence in Manage | Database | Relationships, click the Article_Author box to select it and then click the button with two plus signs.
I actually have 4 tables. Articles, and authors, which I use as a portal in my articles layout. I also have a table of first names and one of last names that I use as drop down lists for entering an author. When I am done creating an index I delete all the records in articles, and authors. The only permanent data are the first and last name tables. I used to have a permanent list of authors, but separate first and last names turns out to be easier to work with. The majority of author names are never repeated.
The order of names is important. I input the names in the order they appear in the Journal TOC, and they have to stay in that order. Editors can be very picky about details! That is why I included an author number (really an author sort number) in the authors table. First one in is #1 and so on from there, starting over at 1 for each new article. I thought I could sort by number (article number, then author number) instead of alphabetically. So, for each article I have linked authors in a set order. Each author record is unique because of it's article key + author number. Each article record has a field with the number of authors for that article. I need to calculate the formatted index entries and put them in a table for export to a .txt file. For each article I have to generate 1 record per author, with each of those records containing all the authors, in rotating positions. Each record will have a calculated field that looks like this, including punctuation
author1, author2,..., and authorlast, article title
and a second field with the page range for the article.
I believe I need a script that, for each article, sorts the related authors and generates a record, then resorts the authors and generates another record. It would stop when it reaches the number of loops = to the number of authors for that article (which is in a field in the article table). Then it goes to the next article and does it all over again. To add just a little complexity to this, articles with 1 and 2 authors are formatted differently--their punctuation is different. In addition, the number of authors vary from article to article, and the last article in the formatted output has different punctuation in front of it than the rest of them.
This was waay beyond my abilities in Access, so I used make table queries to create calculated fields individually for each combination from 1 to 4 authors. After 4 authors I just created more than one record per article and edited them all in my indexing program. If I could do this right it would save me a lot of time.
Below are the entries as they appeared in the index I sent my editor for one article with 7 authors, so you can see what the calculated field looks like that I have to create.
Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, and Srinivasan, Anand. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, and Agrawal, Shipra. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, and Kanthi, C.N. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Ramamirtham, Jeyashankher, Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., and Naidu, K.V.M. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Rastogi, Rajeev, Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., and Ramamirtham, Jeyashankher. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Satkin, Scott, Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, and Rastogi, Rajeev. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
Srinivasan, Anand, Agrawal, Shipra, Kanthi, C.N., Naidu, K.V.M., Ramamirtham, Jeyashankher, Rastogi, Rajeev, and Satkin, Scott. Monitoring Infrastructure for Converged Networks and Services, No. 2, 63–77
I don't see any need for separate tables for first and last names and I wouldn't if I were using Access either. The name information should be in your Authors table and any value lists you define for first and last names can draw their data from those fields in the Author table.
Be that as it may, I believe a script can loop through your articles and authors to create author lists in the rotating alphabetized order you specify. The remaining text can then be appended from other text fields to form you index entries.
We'll focus on this relationship:
Article::ArticleID = Article_Author::ArticleID (specify a sort order that sorts Article_Author records by LastName, FirstName for this relationship.)
Whether and how you link other tables to this won't matter as long as you have one record for each author in Article_Author for a given article that either contains or can access (via a relationship) the Author's name. The index entries will exist in this table so that exporting them forms your text file for your index.
Go To Layout [Articles]
Show All Records
Go To record [ first ]
Set Variable [$Authors ; value: List ( Article_Author::AuthorName ) ]
If [ Not IsEmpty ( Article_Author::ArticleID ) ]
Go To Related Records [show only related records ; from table: Article_Author ; using layout: Article_Author]
Set Field [ Article_Author::AuthorList ; Substitute ( $Authors ; ¶ ; ", " ) ]
Set Variable [ $Authors ; value: List ( RightValues ( $Authors ; ValueCount ( $Authors ) - 1 ) ; LeftValues ( $Authors ; 1 ) ) ]
Go To record [ next ; exit after last ]
Go To Layout [Articles]
Go To Record [next ; exit after last ]
This builds the list of authors portion in the text field AuthorList. From there, it should be a simple matter to define a text field that combines AuthorList with the remaining text in your index.
You are definitely right about the separate tables for first & last names. What I was avoiding was doing what my previous database did: each record was a complete name. For the purpose of choosing a name (or appending it to the table) I wanted them separate. But very late at night it didn't occur to me that I could simply reference different fields in the same table.
The script looks like what I am trying to do, except for sorting alphabetically. The authors are usually not listed alphabetically in the journals, and I need to follow the order in which they are printed. So I guess I have to sort by number instead of name. Thanks for setting me on the right track!
Your 7 author example shows the authors listed alphabetically for the first entry and then the names rotate one author to the left for each subsequent entry. If there's a different order, just specify that order in the relationship and it'll still work as long as the "rotate one name to the left" strategy still gives you the order you need for each subsequent entry for the same group of article authors.