Hello, everybody! I need a simple and smart solution for this case:
I have two tables: Products, with ProductID and NeededQty fields, and SupplierStocks with 6 fields : ProductID, StockA, StockB, StockC,StockD, StockE. Every product from Products table can be bought from 5 suppliers: A, B, C, D ,E in this order of prefference. If the quantity needed for a product exceeds the supplier "A" Stocks, the rest must be bought from supplier "B". If the quantity needed exceeds "A+B" stocks, the rest will be bought from supplier C etc... I have to fill a third table (OrderPlace, with 6 fields -ProductID, OrderA,OrderB,OrderC,OrderD,OrderE) with quantity to order from each suplier for each product. Thank you in advance!

crisan,

Do you use VBA?
How often are you going to run this process?
Are you going to also want to decrease the SupplierStocks Table values when you do this?
Is the OrderPlace table going to hold just this order?

This can be written in VBA pretty simply, but need more info.

Kevin

dartcoach

Dartcoach,
1.I don't have any prefference in solving this issue, can be a querry or VBA code as well.
2.This application will be run twice a day, every day.
3.No, I don't have to update the stocks level in this application.
4.Yes, the OrderPlace will hold just this order.
Thank you!

Crisan,

1. How long will you be there today?
2. Could you send me some test data?
3. Will this be run from a form, I.E. a command button?

Kevin

dartcoach

Kevin, this will be run from a button.

TEST DATA.
Products table:
PID NeededQty
AM002C 400
AM003C 520
AM004B 335
AM005R 500


SupplierStocks.
PID StockA StockB StockC StockD StockE
AM002C 230 200 120 500 200
AM003C 600 400 500 550 500
AM004B 0 0 120 400 0
AM005R 150 160 300 260 0

After running the code, OrderPlace table should look like this:
PID OrderA OrderB OrderC OrderD OrderE
AM002C 230 170 0 0 0
AM003C 520 0 0 0 0
AM004B 0 0 120 215 0
AM005R 150 160 190 0 0

Crisan,

Working on it as we speak, should have something in an hour.

Kevin

dartcoach

Crisan,

Quick question, is your product Id autonumber, number or text?

Kevin

dartcoach

Crisan,

I've got the code working, but depending on the format of your ProductID, it may not work in your system. I can't go any further until you get back to me.

Kevin

dartcoach

Kevin, these PID's are text type. The only important thing I forgot to mention is that PID is Primary Key in each of these 3 tables.

Crisan,

That's not a problem. I will be back to you in a few minutes.

Kevin

dartcoach