Multiple Flags

[Guest]

I am looking for how to design a db that will allow me to have multiple flags
for an event.

I use Mastercook, which is a recipe database. When one enters a recipe, he
can flag whether the recipe is chicken, pork, beef, breakfast, lunch, dinner,
etc. There is no limit to how many flags he can associate with a recipe, so
if the dish has both chicken and pork, one would check both of those boxes.

I am working on a project that I would like to add a similar function. For
each event, I would like the user to be able to flag all state standards that
may apply to it. My question is how do I set this up?

Do I need a unique field for each one and checking the standard will turn it
on? If so, what if there are 100+ standards that could be applied?

In the end, I would like to be able to search based on the standards and
returned all events that have been flagged with whatever I am searching for.
I figure I must be missing how to do this without building a unique field for
each.

I would appreciate any help with this.

Thannks.

 

[Allen Browne]

Create a table that has a *record* (not a field) for each of the possible
options.

Now create a 2nd table that allows you to link the other 2. If your existing
tables are:
- Recipe table, with a RecipeID primary key;
- Category table, with a CategoryID primary key;
then the 3rd table will have fields like this:
RecipeID relates to Recipe.RecipeID
CategoryID relates to Category.CategoryID

Now one recipe can belong to as many categories as you desire. Your Recipe
form will have a subform bound to the RecipeCategory table. The CategoryID
field will be a combo where you can choose as one category on each row of
the subform.

This is the standard way to solve the many-to-many relation (one recipe can
belong to many categories, and one category can contains many recipies.) The
3rd table is called a junction table.

Once you get that working, you will want to filter the main form to only
recipies of one category. For details on how to do that, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[Guest]

Create a table that has a *record* (not a field) for each of the possible
options.

Now one recipe can belong to as many categories as you desire. Your Recipe
form will have a subform bound to the RecipeCategory table. The CategoryID
field will be a combo where you can choose as one category on each row of
the subform.

Thank you for your assistance.

It looks like I had the structure set up correctly.

Now, I need to figure out sub-forms. That at least opens where I need to
look.

Thank you.