472,952 Members | 2,303 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

Assistance with Posting Inventory Average Weighted Cost

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
Nov 13 '05 #1
0 1980

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: royaltiger | last post by:
I am trying to copy the inventory database in Building Access Applications by John L Viescas but when i try to run the database i get an error in the orders form when i click on the allocate...
3
by: Henrik | last post by:
Hi all, I'm a .net developer working with developing automation equipment for the sawmill industry. The main focus of our product line is mechanical and measurement equipment but our clients...
1
by: HEMH6 | last post by:
Weihted Average Write a C program to calculate and print the weighted average of a list of N floating point number, using the formula Xave = F1X1 + F2X2+...+ FnXn where the F's are...
3
by: twin2003 | last post by:
how do i get my program to add,delete and save? i could use help asap thanks i will work on my code if i get to work ill let you all know here is what i have so far // created by Nicholas Baatz on...
10
by: Nightspeace | last post by:
Hello Everyone, I am currently trying to create a 3 month running wieghted average to forecast the comming months. My Table consits of 3 colums (Table ID), (Date),(Monthly Total). (Table...
3
by: Salad | last post by:
http://www.mathwords.com/w/weighted_average.htm At the above link gives an example of a weighted average. It uses the following example: Grades are often computed using a weighted average....
5
by: jamesnkk | last post by:
I want to calculate the average price, I have a cost price table- tblcostPrice as below,Same part no can be purchase from different supplier at different cost. In my tblcostPrice I have 2...
1
by: qiubg | last post by:
There are many nodes in the map, each of which is connected to other nodes. One way traffic for all edges, for instance, from N1 to either N2 or N3, but from N2 or N3 canít directly go back to N1....
0
by: edmund_xue | last post by:
Hello There, I was just working on a project of creating a database for a relative that have alots of clients. His company managed the shares portfolio of clients and he requires the buy price...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.