Stock Processing

[sazd1]

Hi NeoPa
Please accept my apologies again.
I will try not to repeat such mistakes in future and I realize that being an expret you have to tackle so many important issues.
Thanks again for your patience to me.

[NeoPa]

Your polite attitude makes it much easier.

I hope you find all the help you need :)

[FishVal]

....
Please correct this query what I am doing wrong with this. And also I will request you to please modify this query to accomodate the OpeningBalance issue. Because the programme has to display StockReport between some date range and definitely it will have to account for the balance available for the items immediate prior to the date range as very rightly advised by you.
Thanks for all your help and guidance.

The best way to correct it is totally drop it down.
As I've already said you should perform aggregate query on your tables (to get datasets where each record has distinct ItemID) prior to join.
If you have difficulties with subquery syntax, then try to create sequence of queries in you access backend.

Kind regards,
Fish.

[NeoPa]

If you have difficulties with subquery syntax, ...

Subqueries in SQL (in case it helps).

[sazd1]

Hi NeoPa
Thanks. Yes i think the topic of Subqueries will definitely help to understand how subqueries work. I will go through it and will let you know of my problems again.

[NeoPa]

Clearly, you can define QueryDefs and use them as sources in your queries, but it's also possible, using SQL, to include a subquery within your QueryDef. This is explained in more detail in the linked article.

Which approach you proceed with is then down to what you feel most comfortable with.

[justwandering]

FIFO and Average Methods of Stock Valuation

On the subject of Stock Tracking and Processing, I recently implemented fairly sleek logic that can run off of a SQL Query using Purchase and sales from a single table and also give me the Opening and Closing Stock as of any date, which is quite helpful. The logic can also give you the Opening and Closing stock value on waited average method of Inventory.

I was wondering if anyone can help find the value of Goods based on FIFO (first in first out method)

here is what I am doing:
Table Structure:
TxnDate
StockCode
Action (Purchase or Sale)
Qty
Rate
Lot Quantity on Hand = (On every Sale reduce the LotQOH, to track FIFO)

Opening Stock = SUM(PurchaseQty-SaleQty) Where TxnDate < StartDate

Purchase = Sum(Purchase) where TxnDate Between StartDate and EndDate

Sale = Sum(Sales) where TxnDate Between StartDate and EndDate

Closing Stock = SUM(Purchase-Sale) Where TxnDate < EndDate

On the same lines,
Value of Closing Stock = Total Purchase Cost - Total Cost of Goods Sold
which in SQL terms is

Value of Closing Stock = Sum(Purchase.Qt y*Rate) as TPurchaseCost, TPurchaseCost / Sum(Purchase.Qt y)*Sum(Sale.Qty )

--This is = Average Cost of Goods on Hand..

The question is HOW To determine FIFO Cost

[sazd1]

Hi Justwandering
thanks for your reply.
Please check following link for FifoStock. This is absolutely amazing you will definitely enjoy that. It calculates the stock on Fifo basis.
** Edit - Illegal Links to Competing forum removed **
I am working in VB express 2008 with MsAccess as database, And i am trying to find out a query for stock calculations as you mentioned in your reply.
Please have a look on that link and as you already have developed stockCalculatio ns it will be more easy for you to implement that because i have never worked in VBA.
And i hope when you will have a go ahead you will share it with us too.
Thanks again.

[justwandering]

Hi Justwandering
** Edit Links removed **
.

These links don't work.. Am I missing something ?

[sazd1]

** Edit - Links removed **

Ok try the above site link and there search Fifo Stock developed by Khawar

[justwandering]

** Edit - Links removed **
Ok try the above site link and there search Fifo Stock developed by Khawar

Looks sensible.. How may I help ?

[justwandering]

While the solution demonstrates a method of recording FIFO, it is limited to only show the CURRENT FIFO based stock valuation.

The tables cannot show the FIFO stock balance and Stock FIFO value as of a certain date, which is what I am interested in.

I've already posted the solution to calculate Opening and Closing date as of any date. The first thing and decent inventory management will offer is Stock Position as of a Date, which is not necessarily current.

[ChipR]

Sorry, but I'm not familiar with FIFO stock tracking. Can you explain how these values that you want are calculated in general? Then we will know whether your data structure supports the calculations.

[justwandering]

Sorry, but I'm not familiar with FIFO stock tracking. Can you explain how these values that you want are calculated in general? Then we will know whether your data structure supports the calculations.

Due to limited time, I could only point you to an explanation.

http://accountinginfo.com/study/inventory/inventory-120.htm

Bottom line - I need to display the Cost of Goods on Hand as of any day.

[ChipR]

Thanks for the link, that explained very nicely.
When you say goods on hand, isn't that the same as ending inventory as of a certain date?
Cost of ending inventory = Beginning inventory + Cost of purchases - Cost of goods sold