Inventory with FIFO and multiple bins

[16montana]

I am new to the forums so hello all.I have been browsing the forums and Google but I am having little success.I am familiar with Access but by no means an expert.
We have an Oracle database that our Warehouse system runs from but the system is not very configurable so I thought I would try something in Access.I can access the database in Access to retrieve the Products, Warehouse,Bin ( a row with about 5 pallet spaces), Lot number (date), quantities at the time.We have about 300 products and hundreds of bins and 2 warehouses -consigned and non consigned.We get orders multiple times a day going to a set number of customers.Proposal is a linked spreadsheet that will have the Product ordered, qty needed,shipping location and customer.Now the same product could be ordered by multiple customers and shipped to multiple locations. The problem I am trying to solve is I need to create a 'pick list' for the fork truck driver as quickly as possible.first we need to look at the non consigned warehouse for product by lot for FIFO.I would like to , in Access , Decrement to amount ordered from the bin (1 bin will usually have less than than the amount ordered) and continue to decrement until 0.For example: Product 1273812 is being ordered with a quantity on 1000 from customer "EI/AIM" going to Mexico.

Warehouse 60 (non consigned) has 200 in a bin.
1000-200=800
Now I need to look at warehouse 50 (consigned) and 500 in a bin with a lot date of 06/01/10 and another bin with 800 with a lot number of 06/10/10.
800-500=300 (fifo)
300-800=-500 so 300 needed to removed from second bin.
We are now at 0 so move on to the next item which could be the same item but a different customer and shipping location.To prevent lot issues, we want to only list bins that will fulfill the requests.We could have a product in 10 bins but only need it form 2 bins.I know this is a lot to ask for so I won't hold my breath but I always like learning new tricks.



Here is the table structure(pics) and queries I have tried to use:

SELECT SDC_ACTCOST1.PRODUCT_NO, SDC_ACTCOST1.WAREHOUSE_NO, SDC_ACTCOST1.BIN_LOCATION, SDC_ACTCOST1.LOT_NUMBER, SDC_ACTCOST1.QTY_ON_HAND, Pick_List_Update_Query.QTY, Pick_List_Update_Query!QTY-[QTY_ON_HAND] AS Expr1
FROM ([Pick List] INNER JOIN SDC_ACTCOST1 ON [Pick List].ITEM = SDC_ACTCOST1.PRODUCT_NO) INNER JOIN Pick_List_Update_Query ON [Pick List].ITEM = Pick_List_Update_Query.ITEM
WHERE (((SDC_ACTCOST1.WAREHOUSE_NO)="50" Or (SDC_ACTCOST1.WAREHOUSE_NO)="60"))


ORDER BY SDC_ACTCOST1.WAREHOUSE_NO DESC , SDC_ACTCOST1.LOT_NUMBER, SDC_ACTCOST1.QTY_ON_HAND;

[ajetrumpet]

your explanation is pretty easy to understand, but without tables and a structure of at least somekind you probably won't get any response. This is nearly impossible to imagine without looking at something to help you. Also, you might want to try and give it a go yourself first. Access is OK for inventory flow systems like FIFO and LIFO (even weighted avg. systems), but it's not the best. Believe it or not, I think Excel is better.