Calculating Wieghted Average Cost (1 Viewer)

[AJR]

Thanks in advance for even reading through this.

I am still quite new to Access but have managed to build a few
databases. I have used a bit of VBA, primarily to open and close
forms and reports. I am quite confused as to how to approach my
next task. I suspect I may have to take on recordsets.

I'll give a bare bones example of what I am trying to accomplish.

A business buys and sells products but only buys or sells them
one at a time. i.e., every transaction is for one product only.

• There is a table for products containing the fields;
• • ProductName
  • ProductNumber

• Cash is considered to be a product.

• There is also a table for Transactions containing the fields
• • TransactionDate
  • TransactionProductName
  • TransactionQuantity
  • TransactionPrice

• There is a query on the Transactions Table that queries the fields
• • TransactionDate
  • • Sorted oldest to newest
  • TransactionProductName
  • TransactionQuantity
  • TransactionPrice

• There is a form to record all Transactions
• • Puchases are entered as having a + Quantity, Sales, a - Quantity
• The recordset for this form is the Transactions Query
• The form contains the objects
• • TransactionFormDate
  • • TransactionDate field
  • TransactionFormProductName
  • • Combo Box
    • • Looks up data in ProductName field from Products table
      • Puts selection in TransactionProductName field from Transactions
  • TransactionFormQuantity
  • • Field from Transactions Table
  • TransactionFormPrice
  • • Field from Transactions Table
  • TransactionTotal
  • • Textbox
    • • Control Source =Transaction Quantity * TransactionPrice

Because Cash is considered to be a product each transaction requires two entries in the Transactions Form

e.g. A sale of 5 units of Product A

January 5, 2014-----Product A-- -5 ---------- $20.00 --------$100.00
January 5, 2014-----Cash ----- + 100 --------- $1.00 ------- $100.00


So the above records 5 units of Product A going out and 100 units of Cash ($100.00) coming in.

From this it is easy to keep track of Inventory and Cash Balance

Problem

1)

I would like to calculate the Average Weighted Cost of each item in inventory and show it on a form or report.

The formula for this is: (Total Amount Paid) / (Total # in Inventory).
When a sale is made, because we are only dealing with cost, you subtract the # sold from inventory, and
from Total Amount Paid subtract (quantity sold*Average Weighted Cost at time of sale)

So, as I see it, I have to filter out Cash, Sort by ProductName and TransactionDate and then step through each
transaction and, using some form of loop or combination of loops, calculate the Average Weighted Cost for eachproduct, iteration by iteration, in a variable (Array) until all transactions for that product are finished and the variable's
(Array's) value will be the current Average Weighted Cost


2) Once I have that value I have to show it in a form. I'm assuming I can put a variable in a Text Box and show it's
value but I haven't tried it yet.




Again, I'm not looking for any code just an explanation as to how to go about this so I'll know how to approach it.


If you've made it this far--Thanks! I really appreciate it


A/R

 

[Privateer]

AJR: You have given us a lot but you seem to be missing a table, the inventory. Somewhere on your form, you need the starting inventory number, perhaps with the combo box used to select the product. So with your example, user selects product A. The row source for that combo should be the products table linked to the inventory table by product ID. You might also want to filter this query to where inventory is greater than zero. After product A is selected, you grab the current inventory, =ProductOption.column(2) and put that in its own text box on the form. The user wants five items, so that is entered in a second text box. Now the third text box does a little math (current-buying) and shows the remaining inventory. The five can be positive because you are going to subtract the value.

You have me confused with the average weighted cost stuff. Could you give an example of just that calculation. You state that you need to loop through and calculate the cost for each product, why, the person is only buying product A. Try doing a query for product A and posting that here. Otherwise, give an example with real numbers, a before and after the sale, so I can follow the logic. Tough to give you and answer when the concept is not clear.