Help on Subqueries.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

All my queries using 1 table only called "TNLINES

In access queries I use below expresion field name "Qty1":
Qty1: Sum(IIf([Return]=True,[qty]*(-1),[qty])

I have another field "Price":-
Sum(TNLINES.FILEPRICE) AS Pric

I need to create another field called "Selling Price". When I use below statement, I receive error "Subqueries cannot be used in the expresion (Sum([Qty1]*[Price]))"
Selling Price: Sum([Qty1]*[Price]

What is my mistake & is there any other way
Pls advise

Mark Magese
 
Hi!

Selling Price: Sum([Qty1]*[Price])
does not work because Qty1 is calculated field in the
same query. Instead of Qty1, you have to use the same
expression used to calculate Qty1, like this:

Selling Price:
Sum(IIf([Return]=True,[qty]*(-1),[qty])*[Price])

:-)
-----Original Message-----
Hi,

All my queries using 1 table only called "TNLINES"

In access queries I use below expresion field name "Qty1":-
Qty1: Sum(IIf([Return]=True,[qty]*(-1),[qty]))

I have another field "Price":-
Sum(TNLINES.FILEPRICE) AS Price

I need to create another field called "Selling Price".
Click to expand...
When I use below statement, I receive error "Subqueries
cannot be used in the expresion (Sum([Qty1]*[Price]))".
Selling Price: Sum([Qty1]*[Price])

What is my mistake & is there any other way?
Pls advise.

Mark Magesen

.
Click to expand...
 
Hi,
After you the queries as you have advise below:
Sum(IIf([Return]=True,[qty]*(-1),[qty])*[Price])

I still received below error message:-
"Subqueries cannot be used in the expresion "(IIf([Return]=True,[qty]*(-1),[qty])*[Price])".

Pls advise.

Mark Magesen
 
Can you send me the full query SQL definition and
structure of the tables you are using.

Dejan*Mladenovic@EQAO*com

Please replace * with .

:-)
 
Back
Top