Build an order

[Arthur]

I have a traditional table structure linking stores,
products, and orders as follows:

Stores.StoreID
Stores.Address (etc.)

Products.ProductID
Products.Description
Products.ProductType
Products.Promo (a positive integer)

Orders.StoreID
Orders.ProductID
Orders.Quantity

Joins:
Stores.StoreID to Orders.StoreID (1 to M)
Products.ProductID to Orders.ProductID (1 to M)

For any particular Products.ProductType, Products.Promo is
a sequence number indicating ranking (importance), with
1 being highest, then 2, then 3, and so on.

A fourth table is called Survey. It contains information
about the stores - the "ideal" number of units to be
ordered. The fields are:

Survey.StoreID
Survey.ProductType
Survey.Priority
Survey.IdealUnits

Here's how it's supposed to work. Take a particular
ProductType for any particular store, and suppose the
Survey table has this data:

ProductType Priority IdealUnits
WB 1 3
WB 2 2

.... and the Products table has this data:

ProductID ProductType Promo
75 WB 1
84 WB 2

Then the order would be:

ProductID Quantity
75 3
84 2

The "ideal" mix of two products (as shown in the Survey
table) is possible because there are also two
Promo's, as shown in the Products table.

But if the Products table is:

ProductID ProductType Promo
84 WB 1

Then the order would be:

ProductID Quantity
84 5

There is only one Promo for this ProductType. Since the
store has space for 5 (Survey shows 3+2), five of this one
product is ordered.

Finally, if the Products table is:

ProductID ProductType Promo
75 WB 1
84 WB 2
27 WB 3

.... then the order is:

ProductID Quantity
75 3
84 2

There is not space for a third promo for this ProductType
in this store.

Get the general idea?

Right now I have the three tables loaded with data:
Products, Stores, Survey. I think that there must be a way
with queries to create my Orders table. (I don't know VBA
yet).

Is this not possible with queries?
Can someone give me a steer please? For instance I don't
even know whether to try to link the Survey table with the
others or not, and if so how....?

Art

 

[Tom Ellison]

Dear Arthur:

After a while studying your problem description, I think I may have
understood what you want to accomplish. Let's see if I've come close.

Concentrating on just one Product Type at a time, every row in the
Survey has an IdealUnits value that must be ordered. The problem is
to know which product is to be ordered for each of these.

If there are the same number of rows (priorities) in both Survey and
Products you just want to match them. If there are more rows
(priorities) in Survey, then you want to order more of the last
(highest priority) Product for all the excess Survey priorities. If
there are more Product rows than Survey rows, you can simply ignore
the excess Products.

Do I have that right so far?

Assuming I do, or am at least close, lets discuss an algorithm. I'm
suggesting this description:

For each row in Survey, choose the product whose Products.priority is
the maximum value of those Products.priority values less than or equal
to the Survey.priority

I think this will perform what I suggested above. Before we even
start working through the SQL to do this, could you work this through
for all the cases you will face and see if it is correct. Then we can
probably write SQL to do it.

The results would then give a product and quantity for each
ProductType / priority. You indicate you would want to drop the
priority and summarize this over each Product selected, giving a final
result of ProductType / Product / quantity.

Check this out and let me know if we should proceed from this
understanding.

I have a traditional table structure linking stores,
products, and orders as follows:

Stores.StoreID
Stores.Address (etc.)

Products.ProductID
Products.Description
Products.ProductType
Products.Promo (a positive integer)

Orders.StoreID
Orders.ProductID
Orders.Quantity

Joins:
Stores.StoreID to Orders.StoreID (1 to M)
Products.ProductID to Orders.ProductID (1 to M)

For any particular Products.ProductType, Products.Promo is
a sequence number indicating ranking (importance), with
1 being highest, then 2, then 3, and so on.

A fourth table is called Survey. It contains information
about the stores - the "ideal" number of units to be
ordered. The fields are:

Survey.StoreID
Survey.ProductType
Survey.Priority
Survey.IdealUnits

Here's how it's supposed to work. Take a particular
ProductType for any particular store, and suppose the
Survey table has this data:

ProductType Priority IdealUnits
WB 1 3
WB 2 2

... and the Products table has this data:

ProductID ProductType Promo
75 WB 1
84 WB 2

Then the order would be:

ProductID Quantity
75 3
84 2

The "ideal" mix of two products (as shown in the Survey
table) is possible because there are also two
Promo's, as shown in the Products table.

But if the Products table is:

ProductID ProductType Promo
84 WB 1

Then the order would be:

ProductID Quantity
84 5

There is only one Promo for this ProductType. Since the
store has space for 5 (Survey shows 3+2), five of this one
product is ordered.

Finally, if the Products table is:

ProductID ProductType Promo
75 WB 1
84 WB 2
27 WB 3

... then the order is:

ProductID Quantity
75 3
84 2

There is not space for a third promo for this ProductType
in this store.

Get the general idea?

Right now I have the three tables loaded with data:
Products, Stores, Survey. I think that there must be a way
with queries to create my Orders table. (I don't know VBA
yet).

Is this not possible with queries?
Can someone give me a steer please? For instance I don't
even know whether to try to link the Survey table with the
others or not, and if so how....?

Art

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Arthur]

Tom, thanks for your response. You've definitely got the
drift of it.

You say: "The problem is to know which product is to be
ordered for each of these." I'd add "and how many".

"If there are the same number of rows (priorities) in both
Survey and Products you just want to match them." Yes.

.... I want to do the same thing for the final case: "If
there are more Product rows than Survey rows, you can
simply ignore the excess Products". (Or, match them,
ignoring Products table records without a match from
Survey table).

The middle case is more involved. "If there are more rows
(priorities) in Survey, then you want to order more of the
last (highest priority) Product for all the excess Survey
priorities." That works for just a couple rows (which
unfortunately was the example I picked). But if there's
more rows I want to increase the units proportionately for
all records. See my Sept 27 post in the General Discussion
forum.

The ideal solution would be an updatable query that would
allow the user to edit the units. But that may be asking
too much. Might have to do a MakeTable.

Art

 

[Tom Ellison]

Dear Art:

My comments inserted below:

Tom, thanks for your response. You've definitely got the
drift of it.

I feel pretty good for successfully decoding your post. Thanks for
letting me know.

You say: "The problem is to know which product is to be
ordered for each of these." I'd add "and how many".

As far as your example goes, the quantity of each product was just the
quantity in the survey at the matching or higher priority, so I was
not concentrating on that, since that wouldn't be very difficult.

"If there are the same number of rows (priorities) in both
Survey and Products you just want to match them." Yes.

... I want to do the same thing for the final case: "If
there are more Product rows than Survey rows, you can
simply ignore the excess Products". (Or, match them,
ignoring Products table records without a match from
Survey table).

You gave an example of this before:

<quote>

Finally, if the Products table is:

ProductID ProductType Promo
75 WB 1
84 WB 2
27 WB 3

.... then the order is:

ProductID Quantity
75 3
84 2

There is not space for a third promo for this ProductType
in this store.

</quote>

Your more recent statement seems to contradict that. Could you
reconcile this, please?

The middle case is more involved. "If there are more rows
(priorities) in Survey, then you want to order more of the
last (highest priority) Product for all the excess Survey
priorities." That works for just a couple rows (which
unfortunately was the example I picked). But if there's
more rows I want to increase the units proportionately for
all records. See my Sept 27 post in the General Discussion
forum.

Your original post dealt with this case, but only over one product:

<quote>

But if the Products table is:

ProductID ProductType Promo
84 WB 1

Then the order would be:

ProductID Quantity
84 5

</quote>

Could you prepare an example of how more than 2 products would prorate
over more than 3 survey rows and an explanation of the desired
algorithm. Note that, when you round a proration among products (and
quantity must be and integer, right?) and them sum the total of these
rounded values, they well might not add up to the value from the
survey.

Consider this example:

Survey excess quantity: 11
Product 1 (product with priority 1) prorated qty: 5.5 rounds to 6
Product 2 prorated qty: 5.5 rounds to 6
6 + 6 <> 11!!!!!
A simple and often acceptable method is to prorate all except the item
that is largest in the proportion (using perhaps the priority to break
ties) and then assign the value for it from the total (11 above) minus
the sum of all the other results (6 in the above example) so that
Product 1 would be 5 and Product 2 would be 6. You don't have to do
this if you don't require matching the survey number exactly. But if
you do have to match it exactly, putting all the error into one
Product is a simple (though not always desirable) way to handle this.

The ideal solution would be an updatable query that would
allow the user to edit the units. But that may be asking
too much. Might have to do a MakeTable.

One possible way of doing this is to make all the Product / Quantity
generation for the matching priorities first and append them to a
table. From this you can then generate all the unmatched survey items
and append more rows to this table for the unmatched priorities. I
still don't know all the details of how we would do that, but I hope
my comments here are moving us toward that.

Art

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts