I am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham
First Name: John
Last Name: Twain
First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.