Tables and data question.

[Dennis]

If this is the wrong place to post this, I apologize.
I am having a problem that I just can't seem to wrap my brain around
regarding tables and their relationships. Part of the problem is that
I am not sure I can even articulate the question, but here goes.
*
Database: Book_Collection
One table: tblBooks: Fields = Book_ID, Title, Author_ID, Genre_ID
2nd table: tblAuthors: Fields = Author_ID, Author_Name
*
In this instance, there is a one-to-may relationship between the two
tables, but when I enter records in just the Books table, how do I
know which Author_ID I should so that it coincides with what is in the
Authors table?
*
Any help you could give would be greatly appreciated.

D.

 

[Ken Snell]

First, your table structure limits you to a single author per book. Is this
reasonable for your expected data? If not, then you need to add a third
table (called a junction table) that allows you to relate one book to many
authors (one or more), and one author to many books (one or more). This
table's structure would be this:

tblBookAuthor
Book_ID (composite primary key with Author_ID)
Author_ID (composite primary key with Book_ID)


It appears that you're entering data directly into the tables. Don't do
that. Tables are meant for storing data, not for user interface (not even by
the "developer"!).

Instead, create a form that is bound to the books table, and that has a
subform that is bound to the tblBookAuthor table (the form wizard will help
you set this up) and that is linked to the main form via the book ID. Then,
you can use a combo box (the combo box wizard will help you set this up) in
the subform that displays a list of all authors in your authors table, and
lets you select an author and add it to the tblBookAuthor table. This setup
will let you select multiple authors for a single book.

 

[John Vinson]

If this is the wrong place to post this, I apologize.
I am having a problem that I just can't seem to wrap my brain around
regarding tables and their relationships. Part of the problem is that
I am not sure I can even articulate the question, but here goes.
*
Database: Book_Collection
One table: tblBooks: Fields = Book_ID, Title, Author_ID, Genre_ID
2nd table: tblAuthors: Fields = Author_ID, Author_Name
*
In this instance, there is a one-to-may relationship between the two
tables, but when I enter records in just the Books table, how do I
know which Author_ID I should so that it coincides with what is in the
Authors table?
*
Any help you could give would be greatly appreciated.

D.

This design assumes that a book can have one and only one author. At
my right elbow is a copy (well thumbed) of _Access 2000 Developers
Handbook_ by Ken Getz, Paul Litwin, and Mike Gilbert. If this is an
issue for your collection, you may want to consider authorship as a
*many* to many relationship (Ken's written several books, as well as
collaborating with other authors).

That said... your table structure is ok if you limit your collection
to books with one author. But table datasheets are not a good way to
enter data! They should be used *only* for debugging, not for routine
data entry or editing. It's better to use Forms for these tasks. You
can create a Form based on tblBooks; on that Form you would put a
textbox into which to enter the Title, and probably two Combo Boxes
for AuthorID and GenreID. The AuthorID combo box would store the
AuthorID but what you would see on the screen is the author name.

If you're interested in pursuing the many to many idea, post back...
it's just the next step in your design.

 

[Dennis]

Thank you both for your help!
D