Help Doing a Lookup

[mattc66 via AccessMonster.com]

I need to provide my Sales staff with a due date of when product will be
available.

I have two tables see below. I want to create code that will compair the
tblInventory and then the tblPurchaseOrders and display the DueDate of when
we would have stock OnHand.

In the example below we would have Wagon OnHand 12/1/06 and Wheels OnHand
12/1/06.

Does anyone have a suggestion on how to be perform this lookup.

Thanks
Matt

EXAMPLE TABLES BELOW

Table1 tblInventory
Table2 tblPurchaseOrders

tblInventory
Item
Available
Allocated
OnHand

tblPurchaseOrders
PONum
Item
Qty
DueDate

Example:

tblInventory
Item Available Allocated OnHand
Wagon -5 5 0
Wheel -10 10 0

tblPurchaseOrders
PONum Item Qty DueDate
1000 Wagon 4 11/20/2006
1001 Wagon 3 12/01/2006
1002 Wheel 10 11/23/2006
1003 Wheel 5 12/01/2006

 

[Paul B.]

Matt,

It would appear to me that you have some math issues to work on. Perhaps if
I may, your inventory table should read Available 0 and Allocated 5, drop the
OnHand, it is a duplicated of Available (OnHand - Allocated = Available). You
would as a date, say AsOfDate. This would allow you to check against the
purchase orders greater than or equal to that date.

Your query would find all purchase orders equal to or after the AsOfDate in
the inventory. In the form you use to display the DueDate, beside each
purchase order entry, you would subtract the allocated from the order amount
and display that as Available on Date. You would have to keep a running total
if you have more than one PO in case you start with a negative amount (you
have more allocated than arriving on one PO Due Date).

Hope this helps.

Cheers

 

[mattc66 via AccessMonster.com]

Thanks Paul,

You may not understand the math. If you start out with 0 available and then
enter 5 sales orders for the Wagon ( 5 allocations) your available is now -5.
The OnHand is a simple calculation of Available+Allocated. <-5>+5=0 OnHand.
Regardless of the facts I don't have a choice on the data. It comes out of
our ERP system in this fasion.

Thanks for the below.

Matt