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

[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

 

[Van T. Dinh]

Try the Query WITHOU the GROUP BY clause.

 

[John Spencer]

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.

 

[harminder]

Hi John

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

thanks again
- 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 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

 

[John Spencer]

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.

Hi John

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

thanks again
- 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 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