# Thread: How Calculating Weighted Average In Query

1. Registered User
Join Date
Nov 2008
Posts
12

## Unanswered: How Calculating Weighted Average In Query

hi, every body..
how can i calculate the weighted average in query?

Example :
-i have table for income "Purchases"contain: details for Item "Mouse Optical"
_________________________________________
item_code & Qty & Price & Date -------& Value
A1-----------50---- 95----1/11/2008----4750
A1-----------7----- 85----2/11/2008----595
A1-----------5----- 50----3/11/2008----250
_________________________________________
in this case my cost for this item= total Value / Total Qty
cost = 90.24

- and another table for outcome "Sales" contain :
item_code & Qty & Price & Date
A1-----------5---- 100----4/11/2008
in this case my Store Balance from item "Mouse Optical"= Income Qty - Outcome Qty
Store Balance Qty= 57
my store Value= Store Qty* cost
etc...

is there away to calculate a weighted average by query????
thanks for helping me.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Code:
```SELECT item_code, SUM(Price*Qty)/SUM(Qty) as weighted_average
FROM my_table
GROUP BY item_code```
Sorry - I do not understand the second part for sales. You will need to explain that again. Where does store balance come from? Where are your income Qty and outcome qty values? Please explain the second example as clearly as the first.

3. Registered User
Join Date
Nov 2008
Posts
12
Thanks pootle flump,
Code:
```SELECT item_code, SUM(Price*Qty)/SUM(Qty) as weighted_average
FROM my_table
GROUP BY item_code```
this will calculate all Purchases Prices,, Result.. Not true
Where does store balance come from?
from "Purchases Qty - Sales Qty"
Otherwise..
Income - outcome
Where are your income Qty and outcome qty values
value = Qty * Price ' as showed in example

the idea is how to calculate the Store Balance Value???? Only Last Qty + New Qty,, Last Cost Price + New Cost Price..

In Excel ..
Item Cost = (Store Balance Qty * Cost Price ) + (New Purchase Qty * New Price) / (Store Balance Qty+New Purchase Qty )

But How Claculate This In Access?

There Is Idia from Friend:
Code:
```Price:(DSum("[Price]*[qty]";"Purchase";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'")
-nz(DSum("[Price]*[qty]";"Qry_Cost";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'");0))

/(DSum("[qty]";"Purchase";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'")-nz(DSum("[qty]";"Qry_Cost";"[sn]<" & [sn] & " And [Code_Sub_Item]='" & [Code_Sub_Item] & "'");0))```
Where [SN] Placed in Table Of Purchase And Table Of Sales to Define The transactions from Income & Outcome

But when This query run !! It's Calculate Very Slowely And then access Hangup..

I wish I Could explain Clearly..

Thanks Again
Your's
Hany Awad

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ok - one thing at a time
Originally Posted by hongylover
this will calculate all Purchases Prices,, Result.. Not true
Are you saying it is wrong? It returns the value you wanted from the data you supplied - correct?

5. Registered User
Join Date
Nov 2008
Posts
12
I will explain the problem in ur Query..
if we have 5 Qty from Item With cost Price = 90.24 in Store this day .. Ok
tomorrow we sell all the Qty.. ok
store Balance now = 0

we Buy New Qty =10 With New Price = 50
so Last Cost For this Item = 50

Try It in your Query..

6. Registered User
Join Date
Nov 2008
Posts
12
up for advice

7. L33t Helpa Munky
Join Date
Nov 2007
Location
Adelaide, South Australia
Posts
4,049
I don't even understand the problem.

It really doesn't help when you don't answer mods' questions.

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Thank you - I must confess I did give up.

OP - your very first illustration, with the data and expected output, was excellent. Please can you repeat this with your new requirements (new data, required output). I am sure this is possible, you just need to phrase the problem.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•