Fifo batch allocation

[jdraw]

@haroon,

I see you have read the file, and also that you have discussed "the topic" with Gina.
I think it is clear to say that vbaInet , Gina and I have no real idea of what you are asking/seeking. I suggest you attempt to tell us in plain English or by means of a scenario showing inputs and outputs and exactly what you want to see.

If you don't know the logic involved, and you can not find a clear example using Google/Bing, then describing your needs to others seems the only option.

In my own view, FIFO deals with "selling items from the oldest stock first".
So if you did your sales from the oldest received goods (or batches of received goods), I don't see a clear distinction in your needs and FIFO. You may have to select related data from different tables. You would need to record Batch Numbers and items within Batches; you would need historical Prices; Sales would require you to record Item Number/Batch Number.

This link or links within it may help you with the issue.

Good luck.

 

[vbaInet]

jdraw, haroon has got an Excel file in the dropbox link in post #11. Download it and have a look. The last sheet I believe is the output he's after. I understand what he's trying to do in the spreadsheet but just don't understand FIFO. I'm not even sure if that even relates to this so-called FIFO.

 

[haroon.mansha]

@vbalnet I also shared one access file that output is same what I want

Secondly in short I mean to say one Jdraw file focusing on Closing stock with Fifo based price what I need is to find the sales made belonged to which p.inv no. Excel file describe all

 

[haroon.mansha]

@jdraw : I summarize. Your file gives result of stock on fifo basis means qty from different p.inv no.
While I need the supplied /sold qty is from which bill no. Means what was the bill no of the supplied item

 

[haroon.mansha]

Pls refer to file I uploaded
https://www.dropbox.com/s/7malgcn5gdy8bp2/Fifo by sales.xlsx

 

[haroon.mansha]

Any solution

 

[jdraw]

I have just looked at the mdb in your earliest post. I see no comments in any of the code. Even the names of fields don't mean anything to me.
If you understand the Pop, Sop... then please tell us what they mean.

OP does say the report Batch Allocation on FIFO Basis is based on three queries:
QryBatchAllocation, QryPurchaseExtended, QryStartEndPoints

If you have a fully accessible copy of the database, please post.

I have just looked at your xls. I am not an Excel person.

You seem to understand the logic according to your comments in the Report By FIFO.

In most general terms, you buy product in Batches at a specified Price on specific Date. At some point you make a Sale on a certain Date and record it on a BillNO. The quantity for the Sale is taken from the Oldest Stock On Hand. If you have stock from more than 1 batch on hand you take all of oldest Batch(and associated PurchasePrice); then next Oldest batch (and associated PurchasePrice).. until your Sale Quantity is satisfied. You recalculate your StockOnHand (quantity for existing Batches and associated PurchasePrice). For the Sale you have an agreedUpon Selling price and Quantity. You can Take your Quantity and determine the PurchasePrice (cost to you) of that quantity. You can take your SalePrice -PurchasePrice and determine Profit.

In calculations you must ensure you have sufficient quantity to meet Sales. If not, you get into BackOrder situaton etc.

Did you look at the Module in the original mdb in post 7 (I think it was)? There is code (albeit undocumented) for a number of functions that the OP was using.

Good luck.

 

[haroon.mansha]

If you will look my xls you can easily understand what I need result

 

[haroon.mansha]

Yes that's why I did not understand logic due Pop Sop

 

[jdraw]

Here is the code for Globals and Functions in the Module from Post #7.

@vbaInet
Are you able to get a fully accessible version of the mdb from the link in post #7
http://www.access-programmers.co.uk/forums/showthread.php?t=153802

I can see the tables etc using shift, but everything is hidden if I enable the database.

 

[haroon.mansha]

Yes but its very difficult to understand as the developer use non professional approach by not using description. If you can understand the logic pls also let me know as my task if Fifo allocation report on sales

 

[vbaInet]

I can see the tables etc using shift, but everything is hidden if I enable the database.

Yes I can. It's just the Navigation Pane that was hidden which you can enable in Options.

 

[jdraw]

@vbaInet

Thanks. I don't have Access on my new laptop, so will get out the older Dell.
Got it.

@haroon
I just started to look at this. I added your Product (and my own Price) to your xls. I added the Product, Purchase and Sales details to the original mdb and ran the Batch Allocation Report ======> APPLE was not included even though I changed the Report Date Jan-08 to Mar-14,

This means there are undocumented processes (probably queries) that have to be executed to update parts of tables. Something, as yet unknown, to trigger the inclusion of all products, purchases and sales.

I don't have a lot of time today, but I will continue to decipher this.

FYI I added a Price to your Purchase

BatchNo	PDate	   Product	PQty	PPrice	 
1001	01-Jan-14	APPLE	100	10.00	
1002	02-Jan-14	APPLE	200	12.00	 
1003	03-Jan-14	APPLE	300	14.00

and Sales

SInv	SDate	   Product	SQty	SPrice	 
17	05-Jan-14	APPLE	300	13.00	 
18	10-Jan-14	APPLE	250	16.00	 
19	15-Jan-14	APPLE	50	20.00

 

[haroon.mansha]

Upto what I understand is to first step to running total of sales and purchases

 

[jdraw]

@Haroon

I have reviewed the database by the OP. As per #33 I added your data to the database and used some Prices as described previously. Attached is a pdf of the FIFO Batch Allocation of the OP's data and your data.

I am trying to attach a revised version of the database which includes some comments by me to help understand the original code (to some degree). I have included a comments table. You should read these for some understanding of the tables, queries... If you look in module1, you'll see I have added some comments in the code.

The Beginning and ending dates on frmReports have a strange format and input mask. I noted this in the comments. You'll need beginning date 01-Jan-2008 to get OP data, and ending date 31-Jan-2014 to get all of your data. If you only want to see your data, then use beginning 1-jan-2014 and ending 31-jan-2014.

The OP used field level lookups which I don't use or condone. I have looked briefly at his logic, and have tried to describe what I see in overview.

I hope this is enough to get you going.

Good luck.

 

[haroon.mansha]

Thank you , I am looking at it.

Thanks for your help.

 

[jdraw]

Happy to help. I have just started with Access 2010 recently and find the interface quite new and a bit of a challenge to work with. And I'm on Win 8.1 on this new laptop and that interface is "difficult/awkward" ( as thousands before me have said).

I did not change any logic of the OP. I added some comments and error checking. I added your data to the tables(and my prices which were necessary). I looked at tables, forms and modules, then reports, then queries and made comments.

If the reports are what you need, then you have a solution.

The key in my view is his use of the QuantityAllocated algorithm and the QtyBatchAllocated query. The other queries and reports use this QtyBatchAllocated query in combination with other tables and/or queries.
The reports use the queries as their recordsources.

 

[haroon.mansha]

I am also looking the file which you suggested in post #13

Now I have one question. Let's say we want a item history from purchase / sales as below
Product name
Date. Purchase. Sold. Balance

Now problem is that all the date shows in one line from query but I want data in seperate lines as one for purchase one for sales.do you idea how to do it

 

[haroon.mansha]

You can use the suggest file for product A. In a manner purchased sold seperate lines order by date

 

[jdraw]

I do not understand this

You can use the suggest file for product A. In a manner purchased sold seperate lines order by date

Please show a sample of what you are describing. Show the inputs and the desired outputs. You have to make sure there is sufficient data to show what you want.

Earlier in this thread you gave me some sample data, but I had to enter some Prices in order to make sense of the data in the context of FIFO Batch Reports.

Also note that I did not create these databases. I got them through posts and have tried to sort out the logic enough to add a few records and/or answer a question or two.