One Table Relates to Many Fields In Other Table

[Guest]

I am using Access 2002 on Windows XP.

I am working with a table that stores book title information. Each book has
8 columns for author names (Author1, Author2, Author3, etc.) and 8 columns
for author type (AuthorType1, AuthorType2, AuthorType3, etc.). The 8 author
type fields all draw information from 1 table that lists each author type.

How can I create a one-to-many relationship from one table to multiple
fields in another table?

Or is the problem my table design?

 

[John Vinson]

I am using Access 2002 on Windows XP.

I am working with a table that stores book title information. Each book has
8 columns for author names (Author1, Author2, Author3, etc.) and 8 columns
for author type (AuthorType1, AuthorType2, AuthorType3, etc.). The 8 author
type fields all draw information from 1 table that lists each author type.

How can I create a one-to-many relationship from one table to multiple
fields in another table?

You can't, without a lot of complexity. You'ld need to create a query
joining your book table to the Authors table and the AuthorType table,
adding EIGHT instances of each table, using left joins for all sixteen
joins. This will not be updateable and will possibly not work at all.

Your table structure is incorrect.

If you have a many (books) to many (authors), model it as a many to
many relationship!

Books
BookID
Title
<other info about the book as an object>

Authors
AuthorID
LastName
FirstName
<maybe other bio information>

Authorship
BookID ' what book are you talking about
AuthorID ' who wrote it
AuthorType ' what type of author was she

AuthorTypes
AuthorType Primary Key


If a book has five authors, there'd be five records for it in
Authorship; if it has ten there would be ten - and an insuperable
problem for your incorrectly normalized design.

John W. Vinson[MVP]