Let's assume a data model like this:

M:N relationship between books and authors.

Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this piece of code is generated by access 2016):

SELECT book.book_id, book.title, book.year_of_publication,
       book.pagecount, author.firstname, author.lastname
FROM book 
INNER JOIN (author INNER JOIN link_author_book ON author.auth_id =
            ON book.book_id = link_author_book.book
            WHERE (((book.title)="Cassandra: The Definitive Guide, 2nd

the result will be this: Two rows returned when a book with two authors is selected

This is perfectly fine and all as expected. Somethimes allthough its a little bit unpractical. In my case I would like to have the different authors not listed in rows but instead in columns. It should look like this:

enter image description here

My question is: is it possible to build something like this in MS Access 2016 and how can I do that? It is not neccessary that this is done in SQL oder VBA or so, I just want to get an output looking like this :)

Thx in advance

PS: If this is a duplicated question I am sorry but at the moment i don't even know how to call/describe my problem so it is a little bit tricky to google for a solution...



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.