Assistance with Posting Inventory Average Weighted Cost

[Rolan]

I know what I want to do regarding the average cost for inventory, but
need some assistance to sort out some of the details to finalize an
inventory table and query. Essentially, the information is needed for
year end reporting and not for on-going review. Below is an example
that summarizes it all.

tblMain/frmMain /IDTag
tblPurchases/sfrmPurchases/IDPur/PurCost/UnitsPur
tblSales/sfrmSales/IDSales/UnitsSold

Year Purchased Unit Cost Total

2001 500 units $1.00 $500.00
500 units $1.10 $550.00
Total $1050.00

Sold 200 units $1.05 avg $210.00
Left 800 units $1.05 $840.00

---------

2002 500 units $1.20 $600.00
500 units $1.30 $650.00
2001 800 units $1.05 $840.00
Total $2090.00

Avg Cost for 1800 units $1.161

Sold 1500 units $1.161 $1741.50
Left 300 units $1.161 $348.50

---------

2003 500 units $1.40 $700.00
500 units $1.50 $750.00
2002 300 units $1.161 $348.50
Total $1798.50
Avg Cost for 1300 units $1.383

Sold 1100 units $1.383 $1521.30
Left 200 units $1.383 $277.20
I created a Select Totals Query for Purchases showing an accumulative
line by IDTag, SumofPurCost, SumOfUnitsPur and DatePur (w/parameter
beginning/ending date i.e. 1-1-01 to 12-31-01). And another one for
Sales showing IDTag, SumOfUnitsSold and DatePur (w/parameter
beginning/ending date i.e. 1-1-01 to 12-31-01). Then created another
Select Query combining the two above and adding UnitsBalance
[SumOfUnitsPur]-[SumOfUnitsSold].

I further created a new table (tblInventoryCost and frmInventoryCost)
that I want to use for posting the activity at year end by using an
Append Query from the query above. The fields included in the table:
IDInv, IDTag, TotalCost, TotalUnits, TotalUnitsSold, AvgUnitCost,
CostBasis, UnitsCarryforward and CostCarryforward.

The problem is determining the best method for addressing the
Remaining Units and Remaining Cost at the end of each year, and
rolling the data into the subsequent year for calculating the new
average weighted cost. Saving the data seems to be inviting a
potential problem in that if it is posted in error or double posted or
related inventory data is later changed and is overlooked or not
reconciled, then a new problem is created as a result, but if
necessary, I'll deal with it.

Any comments or assistance will be greatly appreciated.

Thanks, Rolan