Question Need to link one item with two different categories

[PamelaJoy]

My database contains a table of many recipes, each recipe belongs to one specific category. I also have a table of many different containers, all (but one) of which is specific to a recipe category (which links the two tables). My query is currently designed to show containers based on each recipe category, supplying their weight and cost for a report. How can I get my query to show that one container, utilized by two different categories, in both instances? I'm afraid if I enter that container twice in the table, each with a unique pk and different category, we may only update the pricing on one or the other in the future, since it's actually the same container.
Is there a smarter more logical way to accomplish what I need to do?
Thank you in advance for your advice and/or insight!

 

[RossWindows]

So Do I understand correctly,

You have three tables;
Categories
Recipes
& Containers

A Category could have more than one recipe
and a recipe could have more than one container?

You may need to have one table that holds possible containers and another that holds the actual container values of each recipe like in the attached db

 

[Mr. B]

PamalaJoy,

It is always those exceptions that make the development of any applicaiton a challange. If everything met one criteria, then everyone could and would be a devleoper.

In your sitiation, you are now experiencing a many-to-many relationship. Anytime that you have this type of relationship, you will have to have a third table to link the data together.

In your case, you would want your third table to have the following fields.
RcptCntrId as a AutoNumber field

RecipeID as a number type field and defined a a Long Intger to serve as a FK to link to the Recipe table (this assumes that you have a AutoNumber type filed in this table named "RecipeID")
ContainerID as a number type field and defined a a Long Intger to serve as a FK to link to the Container table (again this assumes that you have a AutoNumber type filed in this table named "ContainerID").

You will also need a way to manage writing records to this third table as your user attempt to define the Recipes related to Containers and/or the Containers that are related to one Recipe.

HTH

 

[PamelaJoy]

Thank you Ross and Mr. B!
My existing recipe table is similar to what you describe, linking the finished products to the ingredients themselves. I guess I was too focused on keeping the containers separate from the recipes, and wanting them to simply be 'added' in the end, hoping for a quick and easy query to just pull the weight and costing in on a report level. But I can clearly see that this is the way to go - and that it won't be difficult to make the adjustments to make it happen, either.
Thanks so much - sometimes it takes fresh eyes to see the forest for the trees!

 

[Mr. B]

When you are developing any database, you must always make provisions for the exceptions. With that said, sometimes it is difficult to realize what those exceptions really are until you get into the development process.

Do not be afraid to make changes to your database design when required.

Just my ramblings. LOL

Good luck with your project.