Return of old Access Error "you tried to execute a query that does not include the specified express

  • Thread starter Thread starter harmindersingh.singapore
  • Start date Start date
H

harmindersingh.singapore

Hi all,

i've been running a fairly simple query and I keep getting this error.
Here's the query in SQL:

SELECT Order_Product.ORNumber, (([ItemPrice]-[PMCost])*[Quant]) AS
Profit
FROM Product INNER JOIN ([Order] INNER JOIN Order_Product ON Order.[OR
Number] = Order_Product.ORNumber) ON Product.PMNumber =
Order_Product.PMNUmber
GROUP BY Order_Product.ORNumber;

There are 2 tables:
1) Product- the fields are PMNumber, PMDescription, PMQOH, PMReorder,
PMColor, PMStyle, and PMCost
2) Order_Product- the fields are ORNumber, PMNumber, Quant, and
Item.Price

It works if I use "Sum" instead of "Expression" in the Totals row, but
it should work with Expression too, right?

thanks in advance!
Harminder
 
You would use Expression if you were using one of the aggregate operators in
the calculated field.

Sum(ItemPrice-PMCost) * Count(ORNumber) would be an expression in this
context - although I can't think of a reason to use this particular
expression.
 
Hi John

Sorry for the late reply, but I need a clarification. What are the
"aggregate operators"?

thanks again
- Harminder

John said:
You would use Expression if you were using one of the aggregate operators in
the calculated field.

Sum(ItemPrice-PMCost) * Count(ORNumber) would be an expression in this
context - although I can't think of a reason to use this particular
expression.


Hi all,

i've been running a fairly simple query and I keep getting this error.
Here's the query in SQL:

SELECT Order_Product.ORNumber, (([ItemPrice]-[PMCost])*[Quant]) AS
Profit
FROM Product INNER JOIN ([Order] INNER JOIN Order_Product ON Order.[OR
Number] = Order_Product.ORNumber) ON Product.PMNumber =
Order_Product.PMNUmber
GROUP BY Order_Product.ORNumber;

There are 2 tables:
1) Product- the fields are PMNumber, PMDescription, PMQOH, PMReorder,
PMColor, PMStyle, and PMCost
2) Order_Product- the fields are ORNumber, PMNumber, Quant, and
Item.Price

It works if I use "Sum" instead of "Expression" in the Totals row, but
it should work with Expression too, right?

thanks in advance!
Harminder
 
SUM, MIN, MAX, FIRST, LAST, Avg, Count, StDev, Var

Basically anything but the Expression and Group By choices if you are using
the query grid (View: Design View) and have a totals line (View: Totals).
They generally aggregate (combine) data in a group of records and return one
value.


harminder said:
Hi John

Sorry for the late reply, but I need a clarification. What are the
"aggregate operators"?

thanks again
- Harminder

John said:
You would use Expression if you were using one of the aggregate operators
in
the calculated field.

Sum(ItemPrice-PMCost) * Count(ORNumber) would be an expression in this
context - although I can't think of a reason to use this particular
expression.


Hi all,

i've been running a fairly simple query and I keep getting this error.
Here's the query in SQL:

SELECT Order_Product.ORNumber, (([ItemPrice]-[PMCost])*[Quant]) AS
Profit
FROM Product INNER JOIN ([Order] INNER JOIN Order_Product ON Order.[OR
Number] = Order_Product.ORNumber) ON Product.PMNumber =
Order_Product.PMNUmber
GROUP BY Order_Product.ORNumber;

There are 2 tables:
1) Product- the fields are PMNumber, PMDescription, PMQOH, PMReorder,
PMColor, PMStyle, and PMCost
2) Order_Product- the fields are ORNumber, PMNumber, Quant, and
Item.Price

It works if I use "Sum" instead of "Expression" in the Totals row, but
it should work with Expression too, right?

thanks in advance!
Harminder
 
Back
Top