Orders/Inventory Allocation

[Almonds]

At this point, I just need to know if this can be done in Access and whether it would be with query, code, module...??

I am creating a database for a tree nursery. As the trees are actually growing customers are placing orders for certain sizes of trees. When the trees are dug up and measured for delivery, there may not be enough of a certain size to fill customer's orders for that size.

To handle this, each customer order has a field where the customer chooses to handle this be accepting either a smaller or larger tree.

I would like the database to look at the order and if enough trees of a certain size are not available in inventory, to pull the rest of the trees from the smaller or larger size (based on what the customer chose).

Thank you. This one has me puzzled.

 

[Fizzio]

Best way to do this would be with code (+/- query). Use a dcount to count the number of available trees at the size ordered then use a select case / If..then to get the rest of the trees from the next size up/down and adjust the stock levels accordingly. To give the exact code would need the exact structure of your Db etc. Hope this gets you thinking......