Order Shortages based on multiple stages (1 Viewer)

[Ramnik]

Hello Everyone,
I have a production database in which orders are checked for shortages at different levels of production in which :
1 raw product can be fetched in 2 or more final products,
Shortages are checked as per priority.

Please see attached image for illustration.

In this database i have tables named "finalproductlist" and "rawproductslist" in which products are connected in one to many (see table "productmapping"). For E.g. 1 product in raw table can be fetched into 2 or more final products.

I have stocks query on both levels with which shortage is checked.

I want to create the order shortage query which gives the shortage at both levels prioritywise.

Please help

 

[jdraw]

Can you post a jpg of your relationships window? (screen capture)

 

[Ramnik]

I have created this imaginary data to show my point. As the existing structure is too big to display everything.
Tell me what structure you need i will send that.
Do you need relationships between product tables & stock tables?

 

[jdraw]

All relationships and tables.
Send multiple captures if necessary.

You may get some ideas from this free data model from Barry Williams' site.

 

[plog]

Walk me through how the values in Query4.Order1 Raw Shortage get calculated. I can't make sense of the values.

 

[Ramnik]

All relationships and tables.
Send multiple captures if necessary.

You may get some ideas from this free data model from Barry Williams' site.

Please find attached file for relations.

 

[Ramnik]

Walk me through how the values in Query4.Order1 Raw Shortage get calculated. I can't make sense of the values.

The Shortage is calculated in two levels.
Actually the Raw product can be used to create 2 or more different final products.
So we need to calculate the use of raw product based on priority i.e. if the raw product A1 once used in A11 , the quantity available for A12 will be less .

In this example First the Final Product is checked from final product stock deducted from that directly. then checked if the raw product is available for that. if yes then deducted form that raw product stock. Now for the next item the raw product stock available will be less.

 

[plog]

Ok, I see it now. This is a running total query. Search the forum for that term, this has been tons on here.

 

[Ramnik]

Ok, I see it now. This is a running total query. Search the forum for that term, this has been tons on here.

Its not as simple as that. How would you map the raw products with final products and fetch the stock accordingly????

 

[plog]

How would you map the raw products with final products and fetch the stock accordingly????

Query1 to Table3 to Query2

 

[arnelgp]

i think your data is lacking something:
you should put how many raw material (BOM) is required to produce 1 final product in your Product Maping Table.
only then can we compute balances.

scenario:
final product : ballpen
raw materials: plastic (hdpe) how many ml needed to product 1 ballpen
raw materials: silver strip (for logo)-> how many kg. required in 1 pen.
raw materials: carbide tip-> 1 ea for 1 pen.
etc.

 

[Ramnik]

i think your data is lacking something:
you should put how many raw material (BOM) is required to produce 1 final product in your Product Maping Table.
only then can we compute balances.

scenario:
final product : ballpen
raw materials: plastic (hdpe) how many ml needed to product 1 ballpen
raw materials: silver strip (for logo)-> how many kg. required in 1 pen.
raw materials: carbide tip-> 1 ea for 1 pen.
etc.

Its one to one . Its not actual raw material its raw product i.e. semi-finished product .

For example . In a manufacturing process which consists of many processes until final product is made. In that early processed parts can be used in 2 or more final products . But mapping will be one on one. If I have 1 pc of A1 (semi finished), it can only be used in one of A12 or A13 ( finished ).
If I have 10 pcs then I can use some in A12 nd some in A13.

Hope its clear now.

 

[arnelgp]

ok, i get it, i was thinking of "raw" materials. then your only dealing with "assembly" to make one final product.

 

[Ramnik]

ok, i get it, i was thinking of "raw" materials. then your only dealing with "assembly" to make one final product.

Yes. Please guide me.

 

[plog]

How is the A13 Order1RawShortage value 20? Shouldn't it be 35?

 

[Ramnik]

How is the A13 Order1RawShortage value 20? Shouldn't it be 35?

As the total order quantity is 20 for A13 the shortage will be 20 coz we have 0 pcs left in stock. A12 shortage will be 15 . It wouldn't add up coz we need to make 15 for A12 and 20 for A13.

 

[plog]

One more time. Using Query 4, what 2 columns do I use to determine Order1RawShortage?

It's Order1RawStock, but what other column?

 

[Ramnik]

Its Order1RawStock and Order1FinalShortage.

 

[plog]

Attached is a database that takes all the queries/tables you supplied and generates the correct Query4.

Look it over and if you have any questions, let me know.

 

[Ramnik]

Thanks but we cannot use logic based on productId's because the product ids might be any text based or numeric based. It was just filled for understanding purpose.

Moreover the Raw Products and final products are mapped in many to many manner.
Means there might be 2 or more rawproducts which are used to produce 2 or more finalproducts.( sorry if I missed that earlier ).

Thanks.