Calculate price problem

[Guest]

Hi, I have this union query and it' results are
IDInput IDOutput Date Label Inp.Quant. Out.Quant. Price /*
00 01.01.2005 99 100 0
8 /
01 02.01.2005 99 300 0
10 /
01 03.01.2005 99 0 250
0 /
02 04.01.2005 99 100 0
10 /
03 05.01.2005 99 300 0
11 /
02 06.01.2005 99 0 500
0 /

and that works ok, but i must calculate average price (not Avg(Price)) and
Output=[CalcPrice]*[OutQuant] like this, in query:
/* Calc.Price Input Output exp1 exp2([Inp.Q]-[Out.Q])
8 800 0 800 100
9.5 3000 0 3800 400
9.5 0 2375 1425 150
9.7 1000 0 2425 250
10.41 3300 0 5725 550
10.41 0 5205 520 50

Could anyone help me to find calc.Price.Thanks in advance.

 

[Tom Ellison]

Dear Andre:

I repeat some of your samples here, reformatted so I can better read them:

IDInput IDOutput Date Label Inp.Quant. Out.Quant.
Price /*
00 01.01.2005 99 100
0 8
01 02.01.2005 99 300
0 10
01 03.01.2005 99 0
250 0
02 04.01.2005 99 100
0 10
03 05.01.2005 99 300
0 11
02 06.01.2005 99 0
500 0

/* Calc.Price Input Output exp1 exp2([Inp.Q]-[Out.Q])
8 800 0 800 100

I take it this one line of output sumarizes all the above. I don't know
what breaks this data from the input for the other lines of output you
showed.

Now, I believe your calculated price is a weighted average. That is:

100 * 8 = 800
300 * 10 = 3000
100 * 10 = 1000
300 * 11 = 3300
--- ------
39 8100

So, your average price is 8100 divided by 39. Is this correct?

So, the formula is SUM([Inp.Quant.] * Price) / SUM([Inp.Quant.]) Does this
make it work for you?

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Guest]

Dear Andre:

I repeat some of your samples here, reformatted so I can better read them:

IDInput IDOutput Date Label Inp.Quant. Out.Quant.
Price /*
00 01.01.2005 99 100
0 8
01 02.01.2005 99 300
0 10
01 03.01.2005 99 0
250 0
02 04.01.2005 99 100
0 10
03 05.01.2005 99 300
0 11
02 06.01.2005 99 0
500 0

/* Calc.Price Input Output exp1 exp2([Inp.Q]-[Out.Q])
8 800 0 800 100

I take it this one line of output sumarizes all the above. I don't know
what breaks this data from the input for the other lines of output you
showed.

Now, I believe your calculated price is a weighted average. That is:

100 * 8 = 800
300 * 10 = 3000
100 * 10 = 1000
300 * 11 = 3300
--- ------
39 8100

So, your average price is 8100 divided by 39. Is this correct?

So, the formula is SUM([Inp.Quant.] * Price) / SUM([Inp.Quant.]) Does this
make it work for you?

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

Hi Tom, i have 2 tables INPUTTabl(IDInput,Date,Label,InpQuant,Price) for
examle:
00 01.01.2005 99 100 8
01 02.01.2005 99 300 10 ...
and table OUTPUTTabl(IDOutput,Date,Label,OutQuant) -> 01 03.01.2005 99 250
so i create union query and get first example:
IDInput IDOutput Date Label InpQuant OutQuant Price CalcPrice
00 null 01.01 99 100 0 8
8
01 null 02.01 99 300 0 10
9.5
null 01 03.01 99 0 250 0
9.5 etc.
Breaks are not important, i use them because i have no more space to type
example, so I must calculate CalcPrice in query,like this - in first IDInput
(00) CalcPrice=Price (CalcPrice=8), then
Input:InpQuant*CalcPrice
Output:OutQuabt*CalcPrice
exp1:Sum(Input-Output)
exp2:Sum(InpQuant-OutQuant) and CalcPrice:exp1/exp2 (3800/400=9.5)
That 9.5 is my CalcPrice for IDOutput
Thanks