MS Access :Weighted average

Question

I am designing a database for inventory. I have tables holding details for Purchase and Issue. All the items purchased and issued are based on unique "Item Code". The items are issued to various equipments and the cost of the issued item will be booked for that particular equipment.   I am finding problems to calculate the weighted average.

An example is mentioned below :

Purchase Details :

Purchase Date                   Item code         Qty            Unit Price

 June 1, 2012                        111                  10               10                      

June 11, 2012                       111                  10               15                      

June 21, 2012                       111                  10               20

Issue details :                                                                                                             (Below items not in table)

Date                    Item code       Issued to       Qty                                               Opening balance           Qty In Hand                   

June 5, 2012        111                 Equip1        8                                                                  10                              2                                          

June 15, 2012      111                 Equip2        5                                                                  12                              7                      

June 25, 2012      111                 Equip1        6                                                                  17                             11           

The items will be issued on FIFO system. Based on Qty in hand, the issue price will be decided.

• If the Qty in hand is less than qty received in last purchase, the issue price will be the unit price for last purchase.
• If the Qty in hand is more than qty received in last purchase, then the issue price will be the weighted average of the last 2 purchases.                           [(Old Qty*Old Price)+(New Qty*New Price)] / [Old Qty + New Qty]
• If the Qty in hand is more than qty received in last 2 purchases, then the issue price will be the weighted average of the last 3 purchases.

and so on. In other words, the issue price will change after every new purchase

How to design the query to find issue price and should it be stored in issue table or calculated on fly ?

Can someone guide me or provide an sample ?

Answer 1

Thanks for an early reply.

you said . . "average of the last 3 purchases.  and so on. "

"how far back do you need to go?"

It depends on Qty in hand on that date.  Go back n number of records where  "Sum of qty in last n purchases in descending order by date"  is more than the Qty in hand for a minimum value of n .

Answer 2

I think this is really beyond a query.

you would be better off using a form where you can select the item code and in a listbox or subform - show the last X number of purchases and any aother relevent details.

On this form would be a "button" to add a new purchase.

After adding this it could automatically perform the logic to calculate the new price.

you said . . "average of the last 3 purchases.  and so on. "

how far back do you need to go?

I would use the listbox - sorted by date descending.

then just look at each line in turn.

Use the "column" to get data from the listbox.

eg . . Amt_Purchase = me.List_Purchases.Column(2,1)

gets the value from the 3rd colum in row 1 of the list ( column count starts at zero 0 )

hope this get you started . .

cheers

PaulG