Another stock problem I don't know how to approach

[ukdane]

So I have a query that shows the various number of items on an order: (Product Id; NrSold), and the number of items in stock at each warehouse location (ProductId; AreaID; Stock).

I've also sorted these into an order, as stock needs to be pulled from the warehouse in a certain manner (areas with the least stock need to be pulled first).

If there is stock more than or equal to the amount sold in one area, there isn't a problem. BUT if I have less stock avaiable than is sold, I need to know each location, where I can pull the stock from.


There's an example of the query attached (in picture format).
The problem is the where the Pull Result is negative.
For example Product 1003 needs to be pulled
12 units from area C-0-0
40 units from area A-0-0
200 units from area B-0-0
in that order

Product 1002 needs to be pulled.
2500 from area 59-1-2
7505 from area 59-1-1
in that order

How can I create a running total based on the Pull result of the previous areaid of the same productid?

So that for Product 1002 it realises that area 59-1-2 needs to be emptied first, and when that is empty, it needs to remove 7505 additional units from the next area (59-1-1) in order to complete the order?

[OBP]

This could probably made to work with one or more queries, or you could do the whole thing in VBA. How do you intend displaying the output data to the user, on a form or report?
Or just going ahead and making the adjustments to the table values?

[ukdane]

Output will probably be in both form and report, as I want to show the results on screen, and print them off. If I had to choose just one, then it would be a report, but I probably will use a form too.

Once the stock is pulled (the order is placed) I'll need to run a macro to update the stock levels and print a report.

Any ideas how to do it with queries if not, then I'd be happy to hear your VBA approach.

Cheers

[OBP]

The query version would still need VBA to run them and check if the Total Shipment had been met, afterall you may meet the order from just one location.
So it might be easier to do it all with VBA Recordsets.
So you would open a Recordset for each area that has the required Product starting with the least value first, this can just be a simple query.
Subtract the the first stored amount from the requirement and check if it meets the requirement.
If it does, subtract the required amount from the stored amount and Place the product, location and amount in to a Temporary Table for the Form/report and display it.
If the total order is not met move on to the next location and repeat the process until it is met.

[ukdane]

Sounds like a plan!

Now, about VBA Recordsets....

How?

[OBP]

If you can give me a layout of the required tables I can create something for you tomorrow.
A printscreen of the Table relationships as well would help.

[ukdane]

Here's a copy of the basic table relationships.

[ukdane]

I've had a play around, and this code seems to work, in as much as it does the first part of what I want, and I can debug.print The ProductId and AreaID for what needs to be pulled.

Here's the code: (more after)

Sub RecordSetDemo()
'Create a recordset containing all fields and records
'from the Products Table.
Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String
Dim stocklevel As Variant
Dim stocktopull As Variant
Dim remainingtopull As Variant
Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection
mySQL = "SELECT QryAreaStockFlowTotals.productid, QryAreaStockFlowTotals.areaid, QryAreaStockFlowTotals.SumOfstockcolli, TblArea.areapriority, TblArea.areatype, QryProductOrderDetails.[Pallets to Send], QryProductOrderDetails.[Colli to send], QryProductOrderDetails.[Colli Required], QryProductOrderDetails.[Packing Type], QryProductOrderDetails.[Units to be pulled], QryProductOrderDetails.saleorderid, QryProductOrderDetails.saleorderdate " & _
"FROM (QryAreaStockFlowTotals INNER JOIN QryProductOrderDetails ON QryAreaStockFlowTotals.productid = QryProductOrderDetails.productid) INNER JOIN TblArea ON QryAreaStockFlowTotals.areaid = TblArea.areaid " & _
"GROUP BY QryAreaStockFlowTotals.productid, QryAreaStockFlowTotals.areaid, QryAreaStockFlowTotals.SumOfstockcolli, TblArea.areapriority, TblArea.areatype, QryProductOrderDetails.[Pallets to Send], QryProductOrderDetails.[Colli to send], QryProductOrderDetails.[Colli Required], QryProductOrderDetails.[Packing Type], QryProductOrderDetails.[Units to be pulled], QryProductOrderDetails.saleprocessed, QryProductOrderDetails.saleorderid, QryProductOrderDetails.saleorderdate " & _
"HAVING (((QryAreaStockFlowTotals.SumOfstockcolli) > 0) And ((QryProductOrderDetails.saleprocessed) = 0)) " & _
"ORDER BY QryAreaStockFlowTotals.SumOfstockcolli, TblArea.areapriority"

myRecordSet.Open mySQL, , adOpenStatic
'At this point myRecordSet refers to the Products Table.
myRecordSet.MoveFirst 'Go to first record in Products table.
'The loop below prints the contents of the third field from each record.
While Not myRecordSet.EOF 'Top of the loop.
stocklevel = myRecordSet.Fields("Sumofstockcolli").Value
stocktopull = myRecordSet.Fields("Units to be pulled").Value
remainingtopull = (stocklevel - stocktopull)
nextrecord:
If remainingtopull >= 0 Then
Debug.Print myRecordSet.Fields("productid").Value
Debug.Print myRecordSet.Fields("areaid").Value
Else
Debug.Print myRecordSet.Fields("productid").Value
Debug.Print myRecordSet.Fields("areaid").Value
myRecordSet.MoveNext
stocklevel = myRecordSet.Fields("Sumofstockcolli").Value
stocktopull = remainingtopull
remainingtopull = (stocklevel - stocktopull)
GoTo nextrecord
End If
myRecordSet.MoveNext 'Move to next record in recordset
Wend 'Bottom of loop
Debug.Print "All Done!" 'This line not reached until loop is done.
'Close and clean up.
myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub

As you can see, this code uses the query that I showed in the image above (less the last two fields which I removed).

I now need to do 2 more things with the code.
1) Create/append a temporary table showing the ProductId, AreaId, and the amount to pull from each area.
2) Append another table with the revised area totals of remaining stock.

How do I do this?
I currently don't have a table for the first question, BUT for the second question I will append to a table called TblStockFlow
This table includes the following:
stockflowid (an automatic key)
date (this is the date of the order)
areaid (the area)
productid (the product)
stockcolli (the amount of stock that is removed. This MUST be a negative figure)
requisitionid (the order nr)

(as both the order date and the order number are not included in the inital query, I guess they need to be added to it).

Thanks for your help.

Edit: I have succesfully been able to add both the order date and order number to that query.

[OBP]

That looks really good, I am not sure that you need my help :).
Is the database in an SQL Server?
IF not you don't actually need the ADODB connection and Recordset, you could use currentDB and a normal Recordset by dimensioning the MyRecordset as an Object.

What you need now is another 2 recordsets (Or use the same one Twice) to append the data to the other 2 tables using the AddNew command like this example

With rs
.AddNew
![Document Location] = foundfile
![Document Type] = Right(![Document Location], 4)
![Last Accessed] = filedate
![File Size] = Filelength
![Document Name] = docname
.Update
.Bookmark = .LastModified
End With

[ukdane]

Hi OBP,
Thanks for your help, yes I've just managed to create the first table, (the one that didn't exist) Now to try and append to the existing table with negative values.....
Can you see my knees shaking?

:thumb

[ukdane]

Actually with regards to the last table, I can probably create an append query from the new table that I've just created.

[OBP]

Yes, you could, don't forget the Fields to hold negative values should be Double, not Long.

[ukdane]

I've defined those fields as Variant, not Long, or Double will that cause problems?

[OBP]

Not really, Variant just slows Access down a bit while it decides how to store it.
Double is the usual way to store +/- Values particularly if you wan to do Calculations with it.