Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2017
    Posts
    2

    Unanswered: Crosstab query problem

    I have been playing around and trying to get a solution to a book database problem I have and hope I can get some help.

    I have a sorting query that sorts the books by catalog no.

    SELECT tblBooks.CatNo, *
    FROM tblBooks
    ORDER BY fSortValue([CatNo],1), nSortValue([CatNo]), fSortValue([CatNo],3);

    I also have a separate table of authors where by there may be more than one author for each book and use a link table

    SELECT tblBooks_Author.BookID, tblBooks_Author.AuthorID, tblAuthors.Author
    FROM tblBooks_Author LEFT JOIN tblAuthors ON tblBooks_Author.AuthorID=tblAuthors.AuthorID;

    I have tried to create a query a a simple query returns 2 instances of a book for each author but I would prefer it to return for each book Author 1, Author 2, Author 3 etc. along with the catalogue No. and the book title etc. But also without loosing my order by.

    I have added a screen grab so you can see my dilemma

    Is there a way to do this and would appreciate to know how?
    Attached Thumbnails Attached Thumbnails Screen Shot 04-12-17 at 01.31 PM.PNG  

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    604
    Provided Answers: 32
    Your tBookAuthors table should be:
    BookID, AuthorID

    Then the query would join:
    TBooks,tAuthors, and tBookAuthors

    Then the Crosstab would use the above query.

  3. #3
    Join Date
    Apr 2017
    Posts
    2
    Hi Ranman,

    Very many thanks you for your reply which is much appreciated.

    tblBooksAuthor already holds the fields AuthorID, BookID

    I should have been a bit more descriptive in my original post but table books is sorted with the use of a function but never-the-less holds record for 276 books

    If I add the tables tblBookAuthors and tblAuthors to the query the number of returned records is 137 which is clearly not right as it is ignoring the records without a corresponding Author and where there is more than one corresponding Author, the entry is duplicated.

    If I sort alter the join properties I get errors in the function.

    I would still prefer it to show all the books including any where authors haven't been assigned and where the have and there is more than one author to show it in the single row for the corresponding book as Author1, Author2 etc.

    Attached is a image of the table structures I am using and I have included a short version of the Db with the sorting function.

    Click image for larger version. 

Name:	qryBookSorting.PNG 
Views:	4 
Size:	16.4 KB 
ID:	17332

    Book Library.zip

    I am wanting to display the output of the query in a continuous form.

    With thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •