By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,480 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,480 IT Pros & Developers. It's quick & easy.

SQL Statement Help

P: n/a
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.CopyrightYear, Books.ISBNNumber,
Books.PublisherName, Books.Pages, Books.CoverType, Authors.FirstName,
Authors.LastName, Topics.Topic FROM Authors INNER JOIN ((Topics INNER
JOIN Books ON Topics.TopicID = Books.TopicID) INNER JOIN BookAuthors
ON Books.BookID = BookAuthors.BookID) ON Authors.AuthorID =
BookAuthors.AuthorID 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!
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.