SQL Statement Help

[anolith]

Hello Everyone!

I'm wondering if someone could help me out with this problem. We have
a database in Access for our library. We also have it linked to a web
search. If a book is authored by two or more authors, when the results
show up, the book is listed once for each author of that book. If it
is possible, I would like to have the book just listed once, with all
of the authors. I am pasting my code, perhaps it something in my SQL
statement that I can change to allow that to happen.

I am posting links to screen shots of the database structure, and the
results page, to make things more clearer.


SQL CODE:

SELECT Books.Title, Books.Copyright Year, Books.ISBNNumbe r,
Books.Publisher Name, Books.Pages, Books.CoverType , Authors.FirstNa me,
Authors.LastNam e, Topics.Topic FROM Authors INNER JOIN ((Topics INNER
JOIN Books ON Topics.TopicID = Books.TopicID) INNER JOIN BookAuthors
ON Books.BookID = BookAuthors.Boo kID) ON Authors.AuthorI D =
BookAuthors.Aut horID where Books.Title Like '%" & l_title & "%'"

Database Relationship Structure

http://www.rit.edu/~vjl4414/relationships2.jpg

The Results Page

http://www.rit.edu/~vjl4414/resultsscreen2.jpg

SQL Code

http://www.rit.edu/~vjl4414/SQL%20CODE2.jpg

Any help wouuld be grealy appreciated!

Thank You!

[WindAndWaves]

Re: SQL Statement Help

how do you envisage all the authors to be listed in
a. one field
b. different rows
c. different columns
????

it can be all done, but it would help if you decided on this.

[Pieter Linden]

Re: SQL Statement Help

Only way around getting the book title to appear only once is to
either use a summary query by title/ISBN or use the code at mvps.org
to concatenate the authors field in your result set.