vanderghast said:In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price] is itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING clause) which
repeat the computed expression and which DOES NOT use the alias. Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the WHERE
clause is to be evaluated, in general. Sure, if cost is also a computed
expression, you should use the expression, NOT the alias given to the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
Joe said:I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75% profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
I am not using SQL I am using the query form. The problem I believe is I can
not place "where" in the "Total" row since it is an expression.
If I place anything in the "criteria" row then it will prompt me for the
"Cost" and the "sale price" which are in the formula and have values already
assigned to them.
It seems once there is a criteria access no longer knows the values of the
cost and sale price. I would expect it to determine what the "Profit" value
is and conpare it to the criteria. This would seem to be a standard operation
to filter by a calulated value.
vanderghast said:In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price] is itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING clause) which
repeat the computed expression and which DOES NOT use the alias. Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the WHERE
clause is to be evaluated, in general. Sure, if cost is also a computed
expression, you should use the expression, NOT the alias given to the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
Joe said:I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75% profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
John Spencer MVP said:Since you are using TOTALS, you will need to add the expression a second time
and set the Total to WHERE
Field: FilterON:[cost]/[sale price]
Total: WHERE
Criteria: < [Enter Percent as decimal fraction]
As noted earlier, it helps if you post the SQL of the query that is not
working for you.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am not using SQL I am using the query form. The problem I believe is I can
not place "where" in the "Total" row since it is an expression.
If I place anything in the "criteria" row then it will prompt me for the
"Cost" and the "sale price" which are in the formula and have values already
assigned to them.
It seems once there is a criteria access no longer knows the values of the
cost and sale price. I would expect it to determine what the "Profit" value
is and conpare it to the criteria. This would seem to be a standard operation
to filter by a calulated value.
vanderghast said:In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price] is itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING clause) which
repeat the computed expression and which DOES NOT use the alias. Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the WHERE
clause is to be evaluated, in general. Sure, if cost is also a computed
expression, you should use the expression, NOT the alias given to the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75% profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
Joe said:I tried that before it still prompts me for the values for LOTSACC and
COUNTOFQTYRCV
SELECT rec.vid, rec.item, rec.rcvr, Count(rec.qtyrcv) AS CountOfqtyrcv,
Count(rec.dc1) AS CountOfdc1, [CountOfqtyrcv]-[CountOfdc1] AS LotsAcc,
Sum(rec.qtyrcv) AS SumOfqtyrcv, Sum(rec.qtyacc) AS SumOfqtyacc,
([LotsAcc]/[CountOfqtyrcv]) AS Rating, Max(rec.datee) AS MaxOfdatee
FROM rec
WHERE (((rec.datee) Between [Start] And [End]) AND
((([LotsAcc]/[CountOfqtyrcv]))<[enter value]))
GROUP BY rec.vid, rec.item, rec.rcvr;
John Spencer MVP said:Since you are using TOTALS, you will need to add the expression a second
time
and set the Total to WHERE
Field: FilterON:[cost]/[sale price]
Total: WHERE
Criteria: < [Enter Percent as decimal fraction]
As noted earlier, it helps if you post the SQL of the query that is not
working for you.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am not using SQL I am using the query form. The problem I believe is
I can
not place "where" in the "Total" row since it is an expression.
If I place anything in the "criteria" row then it will prompt me for
the
"Cost" and the "sale price" which are in the formula and have values
already
assigned to them.
It seems once there is a criteria access no longer knows the values of
the
cost and sale price. I would expect it to determine what the "Profit"
value
is and conpare it to the criteria. This would seem to be a standard
operation
to filter by a calulated value.
:
In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price] is
itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING clause)
which
repeat the computed expression and which DOES NOT use the alias. Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the
WHERE
clause is to be evaluated, in general. Sure, if cost is also a
computed
expression, you should use the expression, NOT the alias given to the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75% profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
vanderghast said:It is because you CANNOT USE ALIAS in the WHERE clause, but have to repeat
the expression the alias stands for. And you used not one, but two levels of
alias : LotsAcc depends on CountOfqtrce which is itself an alias for a
computed expression.
Furthermore, since the computed expression involves aggregate, some of the
criteria have to be moved in the HAVING clause.
Try:
SELECT rec.vid, rec.item, rec.rcvr, Count(rec.qtyrcv) AS CountOfqtyrcv,
Count(rec.dc1) AS CountOfdc1, [CountOfqtyrcv]-[CountOfdc1] AS LotsAcc,
Sum(rec.qtyrcv) AS SumOfqtyrcv, Sum(rec.qtyacc) AS SumOfqtyacc,
([LotsAcc]/[CountOfqtyrcv]) AS Rating, Max(rec.datee) AS MaxOfdatee
FROM rec
WHERE (rec.datee Between [Start] And [End])
GROUP BY rec.vid, rec.item, rec.rcvr
HAVING (Count(rec.qtyrcv)- Count(rec.dc1))/Count(rec.qtyrcv) < [ enter
value ]
.... if I am right with the parenthesis, that is...
Vanderghast, Access MVP
Joe said:I tried that before it still prompts me for the values for LOTSACC and
COUNTOFQTYRCV
SELECT rec.vid, rec.item, rec.rcvr, Count(rec.qtyrcv) AS CountOfqtyrcv,
Count(rec.dc1) AS CountOfdc1, [CountOfqtyrcv]-[CountOfdc1] AS LotsAcc,
Sum(rec.qtyrcv) AS SumOfqtyrcv, Sum(rec.qtyacc) AS SumOfqtyacc,
([LotsAcc]/[CountOfqtyrcv]) AS Rating, Max(rec.datee) AS MaxOfdatee
FROM rec
WHERE (((rec.datee) Between [Start] And [End]) AND
((([LotsAcc]/[CountOfqtyrcv]))<[enter value]))
GROUP BY rec.vid, rec.item, rec.rcvr;
John Spencer MVP said:Since you are using TOTALS, you will need to add the expression a second
time
and set the Total to WHERE
Field: FilterON:[cost]/[sale price]
Total: WHERE
Criteria: < [Enter Percent as decimal fraction]
As noted earlier, it helps if you post the SQL of the query that is not
working for you.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Joe wrote:
I am not using SQL I am using the query form. The problem I believe is
I can
not place "where" in the "Total" row since it is an expression.
If I place anything in the "criteria" row then it will prompt me for
the
"Cost" and the "sale price" which are in the formula and have values
already
assigned to them.
It seems once there is a criteria access no longer knows the values of
the
cost and sale price. I would expect it to determine what the "Profit"
value
is and conpare it to the criteria. This would seem to be a standard
operation
to filter by a calulated value.
:
In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price] is
itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING clause)
which
repeat the computed expression and which DOES NOT use the alias. Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the
WHERE
clause is to be evaluated, in general. Sure, if cost is also a
computed
expression, you should use the expression, NOT the alias given to the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75% profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
Joe said:Thank you you were correct no alias allowed. it does work. I have one more
simple question to close this. I can place <.75 in the criteria of the
Query
form and it works. if I place
< [enter value]
it does not. What is the correct format if I want all values less than
[enter value].
I could use BETWEEN [LOW] AND [HIGH] were low would always be 0 and high
would be .75. I figure I am missing some punctuation mark
vanderghast said:It is because you CANNOT USE ALIAS in the WHERE clause, but have to
repeat
the expression the alias stands for. And you used not one, but two levels
of
alias : LotsAcc depends on CountOfqtrce which is itself an alias for a
computed expression.
Furthermore, since the computed expression involves aggregate, some of
the
criteria have to be moved in the HAVING clause.
Try:
SELECT rec.vid, rec.item, rec.rcvr, Count(rec.qtyrcv) AS CountOfqtyrcv,
Count(rec.dc1) AS CountOfdc1, [CountOfqtyrcv]-[CountOfdc1] AS LotsAcc,
Sum(rec.qtyrcv) AS SumOfqtyrcv, Sum(rec.qtyacc) AS SumOfqtyacc,
([LotsAcc]/[CountOfqtyrcv]) AS Rating, Max(rec.datee) AS MaxOfdatee
FROM rec
WHERE (rec.datee Between [Start] And [End])
GROUP BY rec.vid, rec.item, rec.rcvr
HAVING (Count(rec.qtyrcv)- Count(rec.dc1))/Count(rec.qtyrcv) < [ enter
value ]
.... if I am right with the parenthesis, that is...
Vanderghast, Access MVP
Joe said:I tried that before it still prompts me for the values for LOTSACC and
COUNTOFQTYRCV
SELECT rec.vid, rec.item, rec.rcvr, Count(rec.qtyrcv) AS CountOfqtyrcv,
Count(rec.dc1) AS CountOfdc1, [CountOfqtyrcv]-[CountOfdc1] AS LotsAcc,
Sum(rec.qtyrcv) AS SumOfqtyrcv, Sum(rec.qtyacc) AS SumOfqtyacc,
([LotsAcc]/[CountOfqtyrcv]) AS Rating, Max(rec.datee) AS MaxOfdatee
FROM rec
WHERE (((rec.datee) Between [Start] And [End]) AND
((([LotsAcc]/[CountOfqtyrcv]))<[enter value]))
GROUP BY rec.vid, rec.item, rec.rcvr;
:
Since you are using TOTALS, you will need to add the expression a
second
time
and set the Total to WHERE
Field: FilterON:[cost]/[sale price]
Total: WHERE
Criteria: < [Enter Percent as decimal fraction]
As noted earlier, it helps if you post the SQL of the query that is
not
working for you.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Joe wrote:
I am not using SQL I am using the query form. The problem I believe
is
I can
not place "where" in the "Total" row since it is an expression.
If I place anything in the "criteria" row then it will prompt me for
the
"Cost" and the "sale price" which are in the formula and have values
already
assigned to them.
It seems once there is a criteria access no longer knows the values
of
the
cost and sale price. I would expect it to determine what the
"Profit"
value
is and conpare it to the criteria. This would seem to be a standard
operation
to filter by a calulated value.
:
In the computed expression column criteria, use
< 0.75
NO quote around it. That should work unless cost or [sale price]
is
itself
another computed expression.
In the SQL view you should have a WHERE clause (or the HAVING
clause)
which
repeat the computed expression and which DOES NOT use the alias.
Ie:
SELECT [cost]/[sale price] AS Profit ...
WHERE [cost]/[sale price] < 0.75
and NOT:
SELECT [cost]/[sale price] AS Profit ...
WHERE Profit < 0.75
since the computed expression, and alias, ARE NOT DONE YET when the
WHERE
clause is to be evaluated, in general. Sure, if cost is also a
computed
expression, you should use the expression, NOT the alias given to
the
expression:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE unitPrice * Quantity/[sale price] < 0.75
and NOT:
SELECT unitPrice * Quantity AS cost, cost/[sale price] AS
profit, ...
WHERE cost/[sale price] < 0.75
Vanderghast, Access MVP
I have a calculated (expression) field in my query as an example
Profit:[cost]/[sale price].
I would like to use a parameter query to find less than 75%
profit.
Placing "Between[min] and [max]" or "<.75" does not fly.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.