MRP requirements vs available

[Blorf]

Hi.

Working on an application that at current works as written, but slow because I wrote vba code to open data as recordsets and scroll through to get my results. I am sure there is a sql solution that would be faster but I don't know how.

Two tables, table one indicates requirements:

Due Date, Item, Qty Required
1/1/06, 123, 10
1/10/06, 123, 5

And Available

Item, On Hand
123, 12

So my result would be to change Table One to read

Due Date, Item, Qty Required
1/1/06, 123, 0
1/10/06, 123, 3

And Table 2 with

Item, On Hand
123, 0

Any advice would be appreciated. As I mention it works using the code, but the code is slow and runs one record in each table at a time. The code follows if interested:

Function Adjudicate()
Set Reqs = CurrentDb.OpenRecordset("BaseRequirements")
Reqs.MoveFirst
Dim MySql As String
Dim UQ As Double
Dim FD As Date

Dim IQ As Integer
IQ = 0

While Not Reqs.EOF()
X = Reqs.Item
Y = Reqs.Due
Z = Reqs.Required
MySql = ""

MySql = MySql & "SELECT Available.ItemNumber, Available.type, Available.When, Available.Reference, Available.Qty FROM Available "
MySql = MySql & "WHERE (((Available.ItemNumber)='" & Trim(X) & "') And Available.Qty > 0);"

Set Avail = CurrentDb.OpenRecordset(MySql)

While Not Avail.EOF() And Z > 0
If Avail.Qty >= Z Then
UQ = Z
FD = Avail.When
Avail.Edit
Avail.Qty = Avail.Qty - UQ
Avail.Update
Else
UQ = Avail.Qty
Avail.Edit
Avail.Qty = Avail.Qty - UQ
Avail.Update
End If

Z = Z - UQ
Avail.MoveNext
Wend

Reqs.Edit
Reqs.Required = Z
If Z = 0 Then
Reqs.Fulfilment = FD
End If
Reqs.Update

Reqs.MoveNext
Wend
End Function






There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

Hi,

It would be much better if you were to state the requirements in prose, rather than code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Blorf]

Cool.

OK, I have a table that lists needs by item, pre sorted by the need date. It contains likely as not multiple instances of the same part, because of differing due dates.

Further, I have a table that contains inventory on hand. I need to, in the requirements table, eliminate the requirements, one record at a time, to the extent that I can based on the inventory available.

Hope that is more clear.

Thanks for the help.


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

I deal with on-hand inventory, requirements & replenishments often.

Why would you want to "eliminate the requirements"? Requirements do not just go away. They get satisfied by existing inventory or replenishment orders.

I often use a UNION query to join inventory (as + values), OPEN requirements (as - values) and OPEN replenishments (as + values).



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Blorf]

I may not be thinking along the path of best practice and my terms may not be accurate.

By eliminating the requirement, I mean to say I want my data to reflect that I have enough inventory to fulfill x requirements, but not Y, and report the Y for PO placement.

Thank you,
Charles Smith

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

Select RQ.[Item], Sum([On Hand]), Sum([RQ Qty])
From 
  [Inventory]    IV
, [Requirements] RQ
Where RQ.[Item] = IV.[Item]
Group By RQ.[Item]
Having SUM([On Hand]) >= Sum([RQ Qtq])

conversly...

Select RQ.[Item], Sum([On Hand]), Sum([RQ Qty])
From 
  [Inventory]    IV
, [Requirements] RQ
Where RQ.[Item] = IV.[Item]
Group By RQ.[Item]
Having SUM([On Hand]) < Sum([RQ Qtq])

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Blorf]

Hi.

Thank you. This would give me, in total, needs after I consider inventory, but will not give me due dates of remaining needs, and would have me order potentially millions in inventory all at once, instead of bringing it in based on actual needs.

This is the reason I wrote the code above. The code works, and with 80K part numbers, takes about 15 minutes to cycle, I just think there is a better way.

I appreciate the help.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

Select
  [Item]
, [Dte]
, Sum([Qty])
From
(
Select
  IV.[Item]
, CDate(1)       As Dte
, Sum([On Hand]) As Qty

From 
  [Inventory]    IV
Group By
  IV.[Item]
, CDate(1) 
 
UNION

Select
  RQ.[Item]
, RQ.[RQ Date] 
, Sum([RQ Qty])*-1

From 
  [Requirements] RQ
Group By
  RQ.[Item]
, RQ.[RQ Date] 
)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[JerryKlmns]

Something on inventory

http://allenbrowne.com/AppInventory.html

Don't know if it helps