Link one field to many categories

[Deanna]

I am trying to set up a database for library books. We would like to be able
to link each book to one or more categories. e.g. one book might relate to
children and to poetry or another book might relate to children and finances.
I cannot figure out how to set up the tables so multiple categories can be
chosen.

 

[Jerry Whittle]

A book can be in many categories and a category can have many books. That's a
Many-to-Many relationship. For this to work you need three tables: Books,
Categories, and BooksCategories to break it down into two 1-M relationships.

The BooksCategories table bridges or links the other two tables. In it you
have foriegn key fields linked to the primary key fields from both the Books
and Categories tables.

Books BooksCategories Categories tables
BookID - BookId, CatID - CatID

 

[KARL DEWEY]

The term "relate" may be throwing you off. You need something like a "key
word" used for text articles such as newspaper stories.
I would do the following --
Create a Category table with a record for each kind like this.
Category ---
CatID - autonumber - primary key
Category - text

In your table for the books include a text field Category. On your data
entry form add an unbound combobox that uses the Category table as source
with 2 columns. Set bound column to 0 (zero). Use the After Update property
to call a macro. The macro to SetValue of book text field Category.
Because you will want multiple entries the value will be
[Forms]![YourDataEntryForm]![Category] & " "
&[Forms]![YourDataEntryForm]![ComboBox] so that it updates with whatever
data is in Category field, a space, and the added selection.

When you search for a Category of book use criteria --
Like "*" & [Enter search number] & "*"

OR if using a form with combobox to start the search use criteria --
Like "*" & [Forms]![YourSearchForm]![ComboBox] & "*"

 

[John W. Vinson]

I am trying to set up a database for library books. We would like to be able
to link each book to one or more categories. e.g. one book might relate to
children and to poetry or another book might relate to children and finances.
I cannot figure out how to set up the tables so multiple categories can be
chosen.

You need THREE tables:

Books
BookID
Title
<other info about the book>

Categories
Category Text <primary key>

BookCategories
BookID
Category

You would add a new RECORD into BookCategories for each category into which a
book falls; you can do this conveniently using a Subform on the Books form,
with a combo box for the category.

John W. Vinson [MVP]