What you are asking goes against the whole point of using a relational database; in short, you should design your tables in a way that minimizes (rather eliminates) the need to redesign the tables. I suggest you read about database normalization.
Your question is, as a matter of fact, an example that I use very frequently when I teach about databases: How would you design a database to hold all the information about books and authors. The scenarios are:
- A book can have one or more authors
- An author may have written one or more books
So, this is a many-to-many relation, and there's a way to design such a database.
First, design a table for the authors:
- tbl_authors
- author_idd (primary key, numeric, preferibly autonumeric)
- first_name (String)
- last_name (String)
Then, design a table for the books:
- tbl_books
- book_id (primary key, numeric, preferibly autonumeric)
- book_title (String)
And finally, a third table is needed to relate authors and books:
- tbl_books_authors
- book_id (primary key, numeric)
- author_id (primary key, numeric)
- main_author (boolean: "Yes" if it is the main author, "No" otherwise)
(Both fields must be part of the primary key)
And now, the main question: How to query for books and its authors?
Asuming the above design, you could write an SQL query to get the full list of books and its authors:
select book_title, first_name, last_name
from
tbl_authors as a
inner join tbl_books_authors as ba on a.authorId = ba.authorId
inner join tbl_books as b on ba.bookId = b.bookId
This way, you'll have something like this:
book_title | first_name | last_name
-----------+------------+-----------
book1 | John | Doe
book1 | Jane | Doe
book2 | John | Doe
book2 | Peter | Who
book3 | Jane | Doe
book4 | Peter | Who
book5 | John | Doe
book5 | Jane | Doe
book5 | Peter | Who
book5 | Jack | Black
Why is this design better than your original idea?
- Because you won't need to alter the structure of your tables to add another author
- Because you don't know a priori how many authors a book can have
- Because you avoid redundancy in your database
- Because, with this design, you'll be able to use front-end tools (like Access forms and reports, or other tools) to create almost any arraingment from this data.
Further reading:
Minor update
This kind of design will help you avoid lots and lots of headaches in the future, because you won't need to alter your tables every time you add a third or fourth author. I learned about database normalization some years ago reading "Running Access 97" by Jon Viescas (this is not a commercial, it's just a reference ;) )... an old book, yes, but it has a very good introduction on the topic.
Now, after you have normalized your database, you can use pivot queries to get what you need (as noted in the answer posted by Conrad Frix).
