Fifo batch allocation

[haroon.mansha]

Ok I will show photo of Excel file then you can easily understand.

 

[jdraw]

As I mentioned before, I am not proficient in Excel.

 

[haroon.mansha]

ok, I give example in plain text, Hope you can understand.


I have two Tables.

1- Purchases 2- Sales

1-Purchases ( I don't need prices so not mentioned, further I giving example of 1 item for easy )

Date PRODUCT QTY Purchasesfrom

01/01/2014 Apple 100 Pcs Applestore
02/01/2014 Apple 200 Pcs Dreamstore




2-Sales

Date Qty PRODUCT SOLDTO

01/02/2014 100 Apple Mr.Abc
02/02/2014 100 Apple Mr.Xyz


DESIRED REPORT / INVENTORY TRACKING

GROUP BY PRODUCT : APPLE

DATE PURCHASES/SOLD RECEIPT ISSUE
01/01/2014 APPLE STORE 100
02/01/2014 DREAMSTORE 100
01/02/2014 MR.ABC 100
02/02/2014 MR.XYZ 100



Now problem is that I can make a query from these two tables, but still problem is that I can not get the query in above sequence, specially I need soldto/purchased to in one column, Can you give idea, how I can make it.

 

[haroon.mansha]

The report which I need will look like this

 

[haroon.mansha]

Update: I have tried union query it works except how I can get separate Purchased / sold thus to write separately on query / report

 

[jdraw]

haroon,

In a typical model your PurchasedFrom would be a Supplier table, and SoldTo would be a Customer table. With all respect it appears to me that you are constantly adding to your requirements. Before getting into the details of BatchAllocation with FIFO Reports, I think you need to sort our exactly what is in scope for your application.

I just do not think you can(should) be working on the intricacies of report formats and FIFO allocation logic and not be aware that Customers and Suppliers are part of the application/data base.

I recommend you write some narrative in plain English(or another language) describing the business, the rules, the processes and the entities involved. FIFO is one method of allocating stock, but regardless of how stock is allocated, you still have Suppliers and Customers. It will help you clarify your thoughts and requirements and will help readers to understand you needs and environment.

What exactly is the nature of the business for which this database is being built? What is your role in this?

I am not in the Logistics/Supply Chain management "business". My background is data base/modelling.

Are you working from the database that I posted in #35 (slight revision to the OP's mdb?

 

[haroon.mansha]

Thanks for your help. I am already working on this database you supplied. Infact it helped me a lot. Thanks
Regarding my 2nd question is just arrive from my 2nd project is to what if we need two seperate values. Can you give idea

 

[haroon.mansha]

Qty allocation is already I am working.This latest question is also for this as the same qtyallocation I want in different way

 

[haroon.mansha]

As you see above I want to get purchased and sold are to seperate . As query is union it shows in same column

 

[jdraw]

Here's a free generic data model of Customers and Orders and Products. It has many more tables some of which may be relevant to your situation. I suggest you work to add all entities and relationships that you need. You already have the "batch allocation query" to help with allocation and FIFO. It's now a matter of incorporating Suppliers and Customers and appropriate queries/reports for your detailed reporting requirements.

See this post for some free videos related to Customers Orders and Products/Items showing design concepts.

 

[recyan]

As you see above I want to get purchased and sold are to seperate . As query is union it shows in same column

Are you indicating that the result of your union query should look like below :

DATE 		PURCHASES/SOLD 		RECEIPT 	ISSUE
01/01/2014 	APPLE STORE 		100		0
02/01/2014 	DREAMSTORE 		100		0
01/02/2014 	MR.ABC 			0		100
02/02/2014 	MR.XYZ 			0		100

Note : Try using the CODE # tags, when posting tabular data.

Thanks

 

[haroon.mansha]

Yes the result is as above. First two records of 1000 are in receipts and other two in issued. If I make union query both values in one column. But I want only these two values in separate column

 

[recyan]

Check if below pseudocode gives some guidelines :

SELECT DateofReceipt as DateA,purchasesFrom as Purchase_Sold, Receipt, 0 as Issue FROM tblReceipts 
UNION 
SELECT DateofIssue as DateA,IssuesTo as Purchase_Sold, 0 as Receipt, Issue FROM tblIssues

Thanks

 

[haroon.mansha]

@jdraw

Can I make sruntot in the example as follows

Only difference is that I make master date Salesm with date (salesm.sdate), and I want to use the main form date in it, how it can possibles, (pls note two tables sales and salesm)

Dim StrSql As String


StrSql = "UPDATE (SELECT Sales.Product, Sales.SQty, Sales.SRunTot,sales.sinv,salesm.sdate "
StrSql = StrSql & "FROM Sales,salesm ORDER BY Sales.Product, Salesm.SDate, "
StrSql = StrSql & "Sales.SInv) as SalesSorted SET SalesSorted.SRunTot "
StrSql = StrSql & "= UpdateRunSum([product],[SQty]);"


BeginRunSumUpdate
CurrentDb.Execute StrSql



If I do as above the result donot comes correct, while if I create the field in child table and use it as reference result comes ok.

 

[haroon.mansha]

I think if we use Inner Join can solve the matter, as I just used and it works ok

Dim StrSql As String


StrSql = "UPDATE (SELECT Sales.Product, Sales.SQty, Sales.SRunTot,sales.sinv,salesm.sdate "
StrSql = StrSql & "FROM Sales inner join salesm on sales.sinv=salesm.sinv ORDER BY Sales.Product, Salesm.SDate, "
StrSql = StrSql & "Sales.SInv) as SalesSorted SET SalesSorted.SRunTot "
StrSql = StrSql & "= UpdateRunSum([product],[SQty]);"


BeginRunSumUpdate
CurrentDb.Execute StrSql

 

[haroon.mansha]

It is working fine. But there is one strange thing.

When I use this query it is ok as all products. but when I filter it , it makes issues turn to "d"

Check if below pseudocode gives some guidelines :

SELECT DateofReceipt as DateA,purchasesFrom as Purchase_Sold, Receipt, 0 as Issue FROM tblReceipts 
UNION 
SELECT DateofIssue as DateA,IssuesTo as Purchase_Sold, 0 as Receipt, Issue FROM tblIssues

Thanks

 

[Gasman]

This is a 7 year old thread.
Best to create your own and not hijack someone else's?

 

[jdraw]

I am attaching a screen capture based on the query and database and function you mentioned.

Here is the function code with comments(copied from the database).

'---------------------------------------------------------------------------------------
' Procedure : QuantityAllocated
' Author    : mellon
' Date      : 2014-08-10
' Purpose   : The purpose of this function is to calculate the Quantity
'of Product Allocated by looking at the count of Product for SalesInv and
'Purchase Batch.
'There are a number of conditions these are identified based on the OP's code
'
'see if there is Product to satisfy the Sale
'1)
' IF SalesInv start >= PurchaseBatchStart AND SRunTot <= PRunTot
' THEN  QuantityAllocated = SRun - Sop + 1
'2)
' IF SalesInv start >= PurchaseBatchStart AND SRunTot > PRunTot
' THEN  QuantityAllocated = PRun - Sop + 1
'3)
' IF SalesInv start >= PurchaseBatchStart AND SRunTot > PRunTot
' THEN  QuantityAllocated = PRun - Sop + 1
'4)
'OTHERWISE  QuantityAllocated = 0
' -----------------------------------------------------------------
'This function is called in QryBatchAllocation.
'
'SQL for  QryBatchAllocation
'
'SELECT Products.ProductDesc, Sales.SDate, Sales.SInv, Sales.SQty,
'Sales.SPrice, [SRuntot]-[SQty]+1 AS Sopn, Sales.SRunTot,
'QryPurchaseExtended.BatchNo, QryPurchaseExtended.PDate,
'QryPurchaseExtended.PPrice, QryPurchaseExtended.Popn,
'QryPurchaseExtended.PRunTot,
'QuantityAllocated([popn],[pruntot],[sopn],[sruntot]) AS QtyAlloc
'FROM Products INNER JOIN (Sales LEFT JOIN QryPurchaseExtended ON
'Sales.Product = QryPurchaseExtended.Product) ON
'Products.ProductId = Sales.Product
'WHERE (((Sales.SDate) Between [forms]![frmReports]![FDate] And
'[forms]![frmReports]![TDate]) AND
'(([SRuntot]-[SQty]+1) Between [popn] And [pruntot])) OR
'(((Sales.SDate) Between [forms]![frmReports]![FDate] And
'[forms]![frmReports]![TDate]) AND
'((Sales.SRunTot) Between [popn] And [pruntot])) OR
'(((Sales.SDate) Between [forms]![frmReports]![FDate] And
'[forms]![frmReports]![TDate]) AND (([SRuntot]-[SQty]+1)<[popn]) AND
'((Sales.SRunTot)>[pruntot]))
'ORDER BY Products.ProductDesc, Sales.SDate, Sales.SInv,
'QryPurchaseExtended.PDate;
'---------------------------------------------------------------------------------------
'Parameters:
' Pop is the Popn===count of Product in Batch based on Purchase RunningTotal
' PRun is the Product running total in Purchase
' Sop is the Sopn ===count of Product in SInv based on Sales running total
' SRun is the Sales Running Total
'
'
Function QuantityAllocated(Pop As Long, PRun As Long, Sop As Long, SRun As Long) As Long

10       On Error GoTo QuantityAllocated_Error
      'see if there is Product to satisfy the Sale
      ' IF SalesInv start >= PurchaseBatchStart AND SRunTot <= PRunTot
      ' THEN  QuantityAllocated = SRun - Sop + 1
20    If Sop >= Pop And SRun <= PRun Then
30        QuantityAllocated = SRun - Sop + 1

      ' IF SalesInv start < PurchaseBatchStart AND SRunTot <= PRunTot
      ' THEN  QuantityAllocated = SRun - Pop + 1
40    ElseIf Sop < Pop And SRun <= PRun Then
50        QuantityAllocated = SRun - Pop + 1

      ' IF SalesInv start >= PurchaseBatchStart AND SRunTot > PRunTot
      ' THEN  QuantityAllocated = PRun - Sop + 1
60    ElseIf Sop >= Pop And SRun > PRun Then
70        QuantityAllocated = PRun - Sop + 1
          
80    Else
      'OTHERWISE  QuantityAllocated = 0
90        QuantityAllocated = 0
100   End If

110      On Error GoTo 0
120      Exit Function

QuantityAllocated_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure QuantityAllocated of Module Module1"
End Function

The function is in Module1. I just opened the database, ran the query and opened module1 to the function.

 

[Pat Hartman]

I haven't looked at either of the two references so they might have working sampls. If they don't here is a summary.

To perform a FIFO (or LIFO) calculation you need a query that selects the item and includes an order by clause that sorts the rows ascending or descending depending on whether you want to do FIFO or LIFO. This essentially a two file match. You have the sale transactions which should be sorted in CUSCIP order if these are stocks. or PartNumber or whatever the item designation is. You will be reading through this table one record at a time. the inentory part is best handled by opening separate queries so you can order the rows ascending or descending OR Sort the sell table first by FIFO or LIFO and process all the FIFOs first and the LIFOs second so that you have a consistent sort for the inventory.

Then you need to create a loop in VBA. Open the query recordset and reading one record at a time, apply the sell amount to the record you are on. If the record goes negative, you need to sell only what was available on that record and move to the next record to sell the remainder. You need to go through the loop until you have created sell records for each batch from which you are selling items.

Batch no. Quantity. Date.
1001. 100. 01/01/2014
1002. 50. 02/01/2014

Now supposingly sales as follows

Date. Quantity invoice no

05/01/2014. 125 1
10/01/3014. 25. 2

so subtract 125 from 100 and find a difference of -25. Create a sale record of batch 1001 for 100. Move to the next batch record and apply the remaining 25. That leaves a balance of 25 which is then sold by the second sale transaction.

You can do other pre-processing. For example, determine if the sale of 125 actually has enough inventory to sell from.

 

[gemma-the-husky]

Hi all

There no idea coming to my mind to how to perform me. That is what I need.

Suppose I have purchase table with following data

Batch no. Quantity. Date.
1001. 100. 01/01/2014
1002. 50. 02/01/2014

Now supposingly sales as follows

Date. Quantity invoice no

05/01/2014. 125 1
10/01/3014. 25. 2


Now the batch reports result I need as follows

Sales. Invoice no 1 100. Batch 1001
25. Batch. 1002

Invoice no 2. 25. Batch 1002

Thanks in advance

I haven't read the whole thread. The real problem is that if your batches matter, then your inventory is not homogenous, and you can't use a standard inventory management system. It needs to deal with batch control.

How do you know that sales invoice 1 DID use 100 items from purchase batch 1, and 25 from purchase batch 2.

Now, if that isn't in fact the case, and the stock IS homogenous, then the real issue is nothing to do with the quantities, but to do with the cost of items you are selling, and the value of the stock. This adds very complex ramifications to an inventory system. You may need to use an average cost system, or even a standard cost system, which is likely to be an expensive project.

And now I see this is an old thread, reactivated for some reason.