Table structure

[lexl]

I'm creating an access database for a bakery that tracks inventory and recipes. Right now they only sell x number of products but that can change in the future. The recipes range from 5 ingredients to 20+ and again, can change down the road.

I believe the best thing to do would be to create one table that holds all items for sale. Then create another table for each item with separate fields being ingredient 1, ingredient 2, etc. When it's time to bake the user inputs the amount and the recipe is generated into a form, saved, and inventory is debited the proper amounts.

Somehow the user needs to update the recipe and add ingredients when needed. Even adding new products whenever they create a new pastry or whatever...I believe the best way to do this would be to have the user add fields through each products' table through a user form. From what I've read though this is a bad idea to let user determine table structure. I see no way around this. Is there a better way to structure what I need? If not how do I set up a form that adds fields to a table?

[jimatqsi]

lexl,
Welcome to Bytes. You'll find lots of good help here but the burden will be on you to help yourself as much as possible. If you see no way around letting users add table fields I suspect you have not spent enough time studying what others have done in Access.

You're right, users should not be adding any fields to your table. You will need to design several related tables that contain all of the needed fields up front. But you're going to have to go one step at a time. Start small, design one table and the form to maintain it. Then move on to the next one.

But before you design anything, spend time studying the example database that came with your copy of Access. All you are needing to do has already been done (in a general sense) in the sample database. It's probably called Northwind.mdb, but you may have others. Check the Microsoft website for your version of Office/Access.

Jim

[GKJR]

lexl,
Jim is right about what he said. It sounds like you have a lot to learn. I'd like to at least point you in the right direction though. I remember being in your same position and making that same mistake.

When you say your users might need to create new fields to accommodate new ingredients in a product you are mistaken. You should have a new table such as "Product Ingredients" with a reference to your "Product" table ID field (a foreign key). Then when you create a record in this table and it is linked to a Product, you can create a query that selects only the amount of records that you have in the Product Ingredients table.

I would go a step further and create a list table called "Ingredients" that lists all of the ingredients the bakery uses. You then have three tables, Products, Ingredients, and Product Ingredients. This last table represents a many-to-many relationship between Products and Ingredients. It needs to have two fields that reference these two tables, plus any additional fields such as quantity, etc.

Good Luck