Help approaching problem - form input of product - send to multiple plant locations

[illicited]

I apologize I'm quite the beginner here. I'm trying to visualize an approach to this problem. What I have is a table that contains plant info (plants that produce a product not vegetation plants). I also have a table that contains all the fields related to the products themselves. Finally I have a third table that contains the constituent items that make up products (think raw ingredients if you will). I have various junction tables set up to define relationships between all these.



What I'm trying to do is create a way of inputting a product and having an option on the form to send that product information to multiple plants. I don't know why but I cannot visualize an approach to doing this. Thoughts would be appreciated. Sorry if this is not specific enough I'll happily provide more information.

Thanks,

illicited

[June7]

Sounds like another manufacturing database question. Frequent topic in forum. Search forum. Recent threads on topic:
https://www.accessforums.net/access/...elp-33469.html
https://www.accessforums.net/program...ess-33403.html

[illicited]

Thank you I will head over there and check those out. Thank you for the response!

[illicited]

I don't think those examples are related to what I'm trying to do. And that's probably my fault for not being able to explain and as a result search this problem well enough. I'm designing the form to input a product, the product is made up of many fields. I would like a way to send all of the data under these product fields to separate plants, so a way to copy all the misc. product fields and just change the plant identifier field in the product input form.

[June7]

Why would you want to duplicate the product data? Just save the product ID.

I don't understand your data structure. If you want to provide db for analysis, follow instructions at bottom of my post. Indicate the objects involved in issue.

[illicited]

I don't understand it either that's the problem. Plants have products, products have constituents. Product ID / Description and some other fields are the same for all plants however the constituents and their amounts will change from plant to plant and from product to product. The database isn't structured right yet or I would post it, I'm still in basic phase. HOWEVER sometimes the constituents do not change from one plant to another, so it would be easy to copy all product/constituent fields to under a new plant ID.

[illicited]

Here's what I have so far.

[illicited]

After further searching it looks like I need to have some sort of nest of 3 forms. One main, one with the junction table fields, and one subform.

[June7]

Guess what - I work in a materials test lab where I was assigned task of developing the lab database. I see many familiar terms in your project.

You have PlantID and PlantName as compound primary and foreign keys. Don't think necessary to save both as foreign key in related table.

Are you building and testing concrete mix designs? Your finished 'product' is a mix blend? Constituents are the component materials for each product? I don't see a table that associates a product with constituents. You have tblPlantProductConstituents. Why do constituents have to be directly associated with plant?

[illicited]

Wild coincidence. I work in quality control for a ready mix concrete and asphalt company.

Basically I'm creating a db to track concrete mix performance mostly. Products would be the mixes, constituents the individual components of the mixes. Constituents don't necessarily have to be associated with a plant unless I plan to keep track of plant inventory of those raw materials later. I'm kind of tinkering to learn, I have many really elaborate spreadsheets that I'd like to centralize. I may be making it to broad just for learning sake.
Some plants have different constituents for the same product. E.g. the rock source is different so the weights are different but it's the same product.

[June7]

So if a product calls for certain % of #3 coarse aggregate (ConstituentID 3300) would that be the requirement regardless of which plant was blending the product?

[illicited]

Yes that's correct. Percentages will stay the same for products across all plants (with rare exception). Weights may be different though because of different densities of #3 rock sources.

[illicited]

I have the form working as I want it to. But now for a way to copy the product info in the main form and the constituent items and their amounts in the subform to a new plant. Would an Append Query be the solution for this?

[June7]

Which form? The db you posted is missing tables the forms depend on. Makes testing difficult.

You want to associate constituent to plant so as to record the quantity required to meet the product % spec?

[illicited]

frmInputProduct should not be missing any dependent tables, it isn't on my end. I only want to associate the amounts of each constituent within each product to each separate plant if that makes sense.