Breaking up multi valued field inputs?

[10kz]

I've got a list box field in a table. This field allows users to select multiple inputs. In my case the listing is:

Supplier A - 10%
Supplier A - 20%
Supplier A - 30%
Supplier A - 40%
Supplier A - 50%
Supplier A - 60%
Supplier A - 70%
Supplier A - 80%
Supplier A - 90%
Supplier A - 100%
Supplier B - 10%
Supplier B - 20%
Supplier B - 30%
Supplier B - 40%
Supplier B - 50%
Supplier B - 60%
Supplier B - 70%
Supplier B - 80%
Supplier B - 90%
Supplier B - 100%

This allows the users to input a 100% or mixed allocation of supplier A or B to a product. What I was hoping to do is break down the allocation to determine volume. e.g:

Product Z Volume 400: Supplier A - 10%, Supplier B - 90%
Product X Volume 200: Supplier A - 50%, Supplier B - 50%
Product Y Volume 250: Supplier A - 100%

I got the first bit working by having the option to multi select the supplier break down in a table. However when I try to work with it in a query I'm out of idea.

Would be interested in how you would tackle this.

 

[jdraw]

Where do Product and Volume fit in your description?

In plain, simple English what is the business and process(es) involved?

 

[10kz]

Its for a company that purchases cooking material from a handful of suppliers.

We know we need 100kg of flour for next month. This is provided to us as a forecast. We then need to be able to easily allocate this 100kg by whatever supplier we choose.

Ideally I'd like to have a table that shows the required 100kg by product (flour) then a drop down which allows me to choose the break down. This then will produce a summary value eg.

Product - Volume (kg) - Supplier Allotment
Flour - 100 - Supplier A 80%, Supplier B 20%

Summary: 80kg Supplier A, 20kg Supplier B

 

[jdraw]

How do you know you need the 100kg of flour?
What lead time do you need to get products ordered and delivered from supplier?
Do you have a list of all Products and Suppliers?
Do you have a list of " what you are cooking to use up Products"?

It would seem you have Orders and "cook items" based on those Orders, but that's just a guess.

I think this sort of questions will help with all the pieces in the puzzle.
Such answers will help with tables/relationships and requirements.

I do not and would not use MVFs.
Good luck

 

[10kz]

The required volume of a product (in this case 100kg of flour) is provided upstream by the client as a forecast.

Lead time is not in this equation.

I have a list of all products in tbl_product and supplier in tbl_suppliers and tbl_forecast which has the client forecasts of required product and volume.


In simplistic terms its a human decision to determine allocation. The manager will see the 100kg of flour and based on their interpretation they will assign the product/volume by supplier.

All I'm wanting to do is facilitate this process via a simple form for them to engage allocate then create a print out showing the decision that was made eg.

Flour 80kg assigned to supplier 1
Flour 20kg assigned to supplier 2

 

[jdraw]

I think this draft model represents the tables involved (as I understand your set up).
If you know the RequestedAmt and the SupplierOfProduct, you can set up a form(s)
to make 1 or more Allocations to meet the Request.

Good luck.

 

[10kz]

Thanks for the diagram jdraw

I've put together the attached database. I've tried to follow the diagram but I'm stuck on the allocation (query?). Can you please see where its not aligning?

 

[jdraw]

I suggest that you watch some free video tutorials by Dr.Daniel Soper that will help with the concepts. You really need to work from a description of your business and build a data model that you can test. What I provided in the previous post was a draft model based on my understanding (best guess) of the business.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Good luck.