Book Database: Books with multiple authors? Advice?

[bfdeal31]

Hi,
I have a question that will probably be fairly simple to answer. I am working on a database to keep track of books and created a simple database that primarily consists of one table "TblBook" which contains the following fields: ID (autonumber), Title, Subtitle, Last Name, First Name, Year Published, Location Published, Publisher, Category (A look up to another table, TblCategoryLookUp), ISBN, and Notes..

My problem deals with books that have multiple authors. I have been dealing with those by putting the name of the first author listed by in the last name and first name fields and I put any other authors in the "Last Name" field (after the first author's last name) followed by commas.. So an example would be like this:
<Last Name>: "Smith, Jane Doe, Frank Murphy" <First Name>: "John"

However the person I was making the database for wants to be able to search by various authors names including those who were co-authors...

I started this project before I knew much about databases and Access and I know my current database is not very efficient. I probably need to create a many-to-many relationship between "tblBook" and something like "tblAuthor" with a bridge entity in between but how would you suggest doing this with the least amount of effort/data re-entry?
I have about two hundred books listed and wanted to know the best way to go about reworking my database. Does anyone have any time-saving suggestions?

I'd appreciate any help I can get and apologize if someone already posted a question like this that has already been answered. Thank you.

 

[TessB]

Create a separate table to store Author Names.
AuthorID, LastName, FirstName
Then create a linking table
BookID
AuthorID

Create a relationship between the Book table and the Author table. ID = BookID
This will allow you to store multiple authors for a single book.

 

[Graham T]

bfdeal31

The attached example should point you in the right direction to the way of approaching the Many-To-Many relationship that is evident between Books and Authors.

HTH

Graham

 

[TessB]

Aren't people here the most generous?

Not only do they answer your question, but often they actually take the time out of their day to work out a nice sample for you!

Bravo, Graham for representing the best of the forum members.



Tess

 

[bfdeal31]

Thank you and question on posting to web?

Hi Graham and Tess,

Thank you very much for your help. This Access Forum has been a great resource. It ended up being a real life saver with an internship I had last year where I had to design a billing/collections invoice database and had trouble getting forms to automatically fill in information based on the entry of account numbers.

I really appreciate the help I've received from random people throughout the world who are nice enough to take time out of their busy days to help me out.

It's been awhile since I've worked with Access so thank you for the example--it helped me get back into the Access mindset. Now I just need to restructure my database and revise the entries I already have. Thanks again.

-Steve

p.s. Do you have any suggestions for putting such a book listing online? I read you can put Access databases on the web but that they only work with Internet Explorer and not Netscape.

 

[bfdeal31]

Setting up the book entry query/form??

Hi again,
I took the advice I was given and reworked my database. It now has the following tables and fields: [tblBook--BookID, Title, Subtitle, Edited, Year Published, Location Published, PublisherID, Category, ISBN, Notes], [tblLinkBookAuthor--AuthorID, BookID], [tblAuthor--AuthorID, Name], [tblPublisher--PublisherID, PublisherName], and [tblCategoryList--Category].

I am now working on creating a form through which to make new book entries. I know I should base the form on a query which I have been working on but I am having trouble getting the form to do what I want. I want to create one form through which all of a book's information is entered. Since a book can have multiple authors I need to include subforms. As the book's author is being entered into the form I want Access to lookup and see if the author is already in the DB and if so, then that new book entry becomes associated with that author as well. If the author does not already exist it should then be added to tblAuthor. I need to have a similar thing happen with Publisher.

The form I created does not work properly--when I go to enter the Author(s) name(s) it says "Field cannot be updated!" I have included a screenshot of the relationships in my database if it's of any use. I'd really appreciate any help I could get. Perhaps I should post my question under the "Forms" or "Queries" section of the forum. Thanks in advance.

-Steve