average cost

[Loizos]

Hi,
I need to calaculate the average cost for the products in a table
i.e


Product Type Quantity Cost StockBalance Value RValu
AvgCost
1 Purchase 10 1.50 10 15
15 1.50
1 sales -2 8
-3 12 1.50
1 sales -3 5
-4.5 7.5 1.50
1 Purcahse 20 1.80 25
36 43.5 1.74
1 sales -6 19
-10.44 33.06 1.74
1 Purchase 20 1.90 39
38.00 71.06 1.82

Every purchase changes the average cost.
Every other transaction is deducted from RValue using the average cost
of the immediately previous transaction

Thanks
Loizos

 

[Sharkbyte]

Loizos:

I would start by suggesting you break this table up.

tblProducts
ProductID (PK)
ProductName
ProductDescription
OnHandQty
AUC

tblTransactions
TransID (PK)
TransType
ProductID (FK)
TransDate
TransRequester
TransQty
TransItemCost
*You can also build a more complex PK, rather than simply the ID, that
would likely be more informative over the long term.

For any (P)urchase transactions, you would take
(((OnHandQty*AUC)+(PurchaseQty*PurchasePrice))/NewOnHandQty) then update
your product record's AUC and OnHandQty.
Insert purchase quantity into TransQty.
Insert item cost into TransItemCost.

For any (S)ales transactions, you would take
Insert transaction table TransAUC with ProductAUC.
Insert transaction table TransQty with SalesQty.
(SalesQty*ProductAUC) for transaction cost
((SalesQty*ProductAUC)*-1) for RValue (inventory value) change
(ProductOnHand - SalesQty) for NewOnHandQty, then update the product record.

You don't need to store RValue (inventory value) as it can easily be
calculated at any time. ((OnHandQty*AUC) for product or
(TransQty*TransItemCost) for sales.)

I think this is what you were asking about...

HTH

Sharkbyte

 

[loizos]

Sharkbyte,
Thank you for your answer
I have some qwestions for you
How I will link the two Tables?
What is the AUC field?
How I will update the product record? Using Query or code?

Thanks

Loizos

 

[loizos]

SharkByte

I am Waiting your Answer

 

[mickeywks]

I'm not Sharkbyte, but you would join the Primary Key ProductID from the
products table to the Foreign Key ProductID in the transactions table.

 

[loizos]

Joseph

Thank you for you answer
In my table i have transactions from diff products
The only transaction that i have price is the purchase trans.
how i will compute the value=price*qty for the other transactions?

Thanks

 

[loizos]

Joseph

The situatiion is the following

My data are :

line Prod Trans Qty Purchase
No Code Type Cost

1 510 Purchase 10 1.30
2 510 Sales -2
3 510 Purchase 30 1.50
5 512 purcahse 100 2.50
6 512 sales -25
512 purchase 250 3.00
513
514

I need to calculate the Average Cost for each product.

line Prod Trans Qty R Purchase Value RTot ACos
No Code Type Qty Cost

1 510 Purchase 10 10 1.30 13 13 1.3
2 510 Sales -2 8 -2.6 10.4 1.3
3 510 Purchase 30 38 1.50 45 55.4 1.45
4 510 sales -20 18 - 29 26.4 1.46
5 512 purcahse 100 2.50
6 512 sales -25



i.e line 1 average cost= Purchase Cost=1.30
line 2 average cost=Purchase Cost=1.30
line 3 average cost=Rtotal/Rqty

 

[Sharkbyte]

Loizos:

To answer your questions:

1. The tables are linked with the ProductID (FK) field, in your
Transactions table.

2. AUC - Average Unit Cost

3. You are probably in a better position to answer this question, but my
first response would be to use code, in the AfterUpdate of a control on your
input form. Which control? I do not know. I don't have enough information
to answer that.

Good luck

Sharkbyte

 

[loizos]

SkarkByte

Thanks for your help

I will use code to calculate the Average cost

The statement is the follwing
If [type]=1 then
([OnHandQty]*[AUC]......... then

endif

The field [OnhandQty] and [AUC]
belong the table tblProducts
How I will refer to them if my statement
is on tblTransaction Form???????

Thanks

 

[loizos]

:

Mickywks
Thank you

Let me ask you something else

I have 2 tables
tblHeader
ProductId
Quantity

Tbl Details
Date
ProductId
PurchaseQty
I have input form for tblDetails

Using code I need to update the tblHeader[Quantity]
i.e
aa=tblHeader[Quantity]+[PurchaseQty]
send me the correct statement

Thanks

 

[Douglas J Steele]

You shouldn't be storing a computed field like that. What happens when
somebody adds additional data to tblDetails and forgets to update the
Quantity field in tblHeader?

Instead, create a query that computes the totals and use the query wherever
you would otherwise have used the table.