I have a db created for a spare parts shop. The main thing disturbing me is the Average Purchase Price. The Db contains lot of forms and tables but I would like to mention here only those that concern with avg pur price.
Header and Footer forms are related on one-to-many basis.
F_PurInvHeader contains following fields
a. InvNum (Autonum – One-to-Many relationship)
F_PurInvFooter contains following fields.
c. Stock (This is calculated field and producing correct result)
Now what I did and what my client need is as follows
Client want to appear Average Purchase Price once he started entering purchase entries. Example: Item A purchased 3 times with different prices
(Table is available in the attached documents to view how Avg Pur Price is calculated)
This is ok. I somehow handled it to display the Avg Pur Price. Then main problem occurred when the stock became ZERO and then again, we purchased same items. This time it was considering all the previous purchase quantities and calculating avg pur price.
Let us say the last avg pur price was 1.26 but once the stock becomes zero and purchase same item next time, the average purchase price should be the one that we purchased
If we purchased item A , 3 pcs at the rate of Dollar 1.75. So the Average purchase price will be 1.75 as long as we don’t make another purchase entry.
But my code considering from beginning qty…say 16+12+4 and finally + 3 which becomes 35 and it is summing all the amount and dividing to get avg pur price which is totally wrong.
Considering above table, the last avg pur price comes 1.28 which is ok but once the stock is ZERO it should sum the purchase qty and price only from when the stock became zero while it wrongly calculating as follows.
The new avg pur price is coming as 1.33 which is totally wrong. It should be 1.75 for the 3 pcs we purchased after ZERO stock.
I am fed up of this situation. I would be gratefull if some can help me in this regards. If needed I can upload here my db with least records and objects. I am sure it will be more clear after seeing my real db.
Usually I would say that the reason for your problem is that the data is not normalized. It is true that your data is not normalized - there are many good articles written on normalization of data, and I suggest that you read up on the subject and modify your table accordingly - but that's not the main problem, the way I see it.
The way I understand the situation is that you're attempting to both record purchases and control inventory using the same fields. When stock goes to 0 you have issues with the data.
I suggest that you keep the table as a purchasing record only, and design a new table for inventory control. All stock additions and withdrawals should be added/subtracted to/from that table only.
I have taken a step towards normalization. I feel that my tables are simple now after some changes I made as below.
I removed calculations over stock field in F_PurInvFooter form and kept is as numeric field that will receive data from table ProductMaster upon updating of ItemName Or ItemCode in F_PurInvFooter . Because I have created another field by Stock in T_ProductMaster which stores the calculated Stock each time when there is purchasing.
In other words, once we purchase some quantity of a particular item, it would update calculate its current available stock and stores in T_ProductMaster. This process is being carried out thru few code lines on after update event of Qty field in F_PurInvFooter.
So now the stock showing after update of ItemName Or ItemCode is coming directly from tbl T_ProductMaster.
But my main concern is avg pur price. How can it be calculated when Stock goes Zero.
I think that there should be a date field in tbl T_ProductMaster that would record the last purchase date that comes from last purchasing of concern item in F_PurInvFooter. So when user updates ItemName Or ItemCode in F_PurInvFooter , it should check in tbl T_ProductMaster for stock and its last purchase date. If stock is previously stored as ZERO and there is last purchase date, it should only sum all purchased qty after that date from F_PurInvFooter. Else otherwize should count all the quantities since begining.
I'm afraid you might have misunderstood my intent. In order to normalize the data so you can do what you want, you need to have two separate, independent tables, one for inventory control and one for purchasing control. They do, of course, have common keys, but that's about the only thing they have in common.
The purchasing table would have the following fields (at a minimum. You will need more fields to satisfy other user needs):
purchase $ total amount
average price this item
average price this item to date
(I know I'll be taken to task for this, because many users - myself included - believe that 'calculated values have no place in a table.' However, guys, I think it's different here, because I think Ashfaque's user wants to be able to identify the trend of the average purchase price as well as the average price itself. In order to have this info, he needs to record it in each purchase record, as it's tedious to re-create it.)
The inventory detail table would look like this (at a minimum, as above):
added/subtracted (need to know if it's an addition to stock or a withdrawal)
An additional inventory master table would have this structure (at a minimum, as above):
current stock (calculated on the fly each time a transaction takes place in the detail table).
Thus the record you presented in your original post, which allows three transactions to be recorded - which prompted me to say the data isn't normalized - would shrink to only one transaction per record, and be normalized to the third level. All average data would be calculated programatically, either in VBA or in invisible cotrols on the form.
Additionally, if I were king, I'd do it this way. When the item is received, only the purchase table is updated. The inventory table would only be updated when a stock clerk enters the item into the stock room and records the transaction in the program.
I apologize if my original intent wasn't clear. Good luck,