item in multiple locations

[bkrencisz]

Hello,



I have a database with about 6000 items for bakery purposes. I have multiple items that are used in different departments. I would like to know how to set up(if possible) these items so I can inventorize these without having to re-create the same items.

Thanks

[aytee111]

Create a table which stores all the items, with an autonumber ID field which is then used in the other tables. There would also be a table for departments and another for usage.

[bkrencisz]

Create a table which stores all the items, with an autonumber ID field which is then used in the other tables. There would also be a table for departments and another for usage.

this has already been done. when inventory is to be done, I print out inventory sheets for each departement. let's say the item PECANS are used in 3 different departments. each departement will have a quantity for this item. when sheet come back, i want to be able to enter amounts by departement in the system and then have a total. currently I have items targeted with their own departement but my issue is with the items that are used in multiple departments.
thanks

[orange]

?? Not sure what you're really asking.
But if you have some material in Location A, some in Location B and some in Location X, then the Total amount of that material
Total material = LocationA Material +Location B Material.... + Location X Material.

I'm not sure what giving the sheets to different groups and getting completed sheets from them and entering data means in practice.
Time involved; issues that arise; lack of material???

It seems if different groups can/do use various amounts of material Q, then if you have a centralized inventory and a projection of how much each group plans to use you can get a handle on what should/could be ordered. If you get actual usage figures. you can adjust your order amounts accordingly. With some historic usage data you can get better predict the quantities of material(s) required.

Perhaps more info(details) on the business would be helpful to readers.

Good luck.

[bkrencisz]

I work for a local bakery. Within the bakery we have different departments that complete the fabrication of a cake. example; 1. Ingredient prep; 2. oven 3, cake assembly and finish 4. wrapping , 5, distribution. In my Access database, I have about 10 tables set-up; suppliers, products, purchase order, product categories, product sub-categories, inventory sections, etc.


We do an inventory count every month. The way that it is currently setup is when I request my inventory form it asks for which departement. I specify a departement and it prints a form with all items attributed to this department. Once I get the inventory back filled in, I have a query that asks me for which departement, I specify a departement and I fill in the info. My question is concerning the items which are used in multiple departements. I cannot seem to be able to attribute more than 1 departement per item. example item PECAN is used by 3 departements. But in my products table I cannot give it more that 1 location.

I hope the additionnal info helps.

thanks

[orange]

It sounds very much like you tables and relationships do not match your requirements.
Start with a clear description of your business and add details.
I recommend you review
-many to many relationships, and
-junction table

Here is a link that I often suggest for database design/planning. The info may be useful to you.


If you post a jpg of your database design, readers will offer comments.

Good luck