Select Reccords based on Expression Results

M

Marktheshark

Hi,

I have created a Select Record Access Query and have and Expression that
calculates a value based on two of the table fields. This caluclation is okay
when I review the output but I want to only select records with a certain
value (i.e. <50). The problem is that when I enter 50 in the Criteria cell
under the Expression and run the Query I get an Enter Parameter Value box!

What am I donig wrong?

I would be greatfull for any help.
 
J

John W. Vinson

Hi,

I have created a Select Record Access Query and have and Expression that
calculates a value based on two of the table fields. This caluclation is okay
when I review the output but I want to only select records with a certain
value (i.e. <50). The problem is that when I enter 50 in the Criteria cell
under the Expression and run the Query I get an Enter Parameter Value box!

What am I donig wrong?

I would be greatfull for any help.

Where did you put the expression? Please open the query in SQL view and post
it here.
 
M

Marktheshark

Thanks for your response. The expression is in the feild cell of the Query
Design View and the value comparision in the Criteria cell. Here is the SQL:

SELECT F4801.WAMCU AS Branch, F4801.WADOCO AS [WO Number], F4801.WALITM AS
[Item Number], F4801.WAUORG AS [Order Qty], F4801.WASOQS AS [Qty Completed],
F4801.WASRST AS [WO Status], F4801.WAUSER AS Originator, (([Qty
Completed]-[Order Qty])/[Order Qty]) AS Expr1
FROM F4801
GROUP BY F4801.WAMCU, F4801.WADOCO, F4801.WALITM, F4801.WAUORG,
F4801.WASOQS, F4801.WASRST, F4801.WAUSER
HAVING (((F4801.WAMCU)=" 104910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 107910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 144910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 104910001") AND ((F4801.WASRST)="95"))
ORDER BY F4801.WAMCU, F4801.WADOCO;
 
J

John Spencer

A group by (Totals) query is unnecessary since you are not aggregating
(combining) any of the fields.

Try the following
-- Replace the reference to the field alias with the actual field names.
-- Drop the Group by
-- Change the HAVING clause to a WHERE clause
-- Add DISTINCT to the query if you are getting duplicate values and
want them reduced to one row.

SELECT DISTINCT F4801.WAMCU AS Branch
, F4801.WADOCO AS [WO Number]
, F4801.WALITM AS [Item Number]
, F4801.WAUORG AS [Order Qty]
, F4801.WASOQS AS [Qty Completed]
, F4801.WASRST AS [WO Status]
, F4801.WAUSER AS Originator
, (([WASOQS]-[WAUORG])/[WAUORG]) AS Expr1
FROM F4801
WHERE F4801.WAMCU IN (" 104910201"," 107910201"," 144910201", "
104910001")

AND F4801.WASRST="95"

AND ([WASOQS]-[WAUORG])/[WAUORG] < 50

ORDER BY F4801.WAMCU, F4801.WADOCO

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for your response. The expression is in the feild cell of the Query
Design View and the value comparision in the Criteria cell. Here is the SQL:

SELECT F4801.WAMCU AS Branch, F4801.WADOCO AS [WO Number], F4801.WALITM AS
[Item Number], F4801.WAUORG AS [Order Qty], F4801.WASOQS AS [Qty Completed],
F4801.WASRST AS [WO Status], F4801.WAUSER AS Originator, (([Qty
Completed]-[Order Qty])/[Order Qty]) AS Expr1
FROM F4801
GROUP BY F4801.WAMCU, F4801.WADOCO, F4801.WALITM, F4801.WAUORG,
F4801.WASOQS, F4801.WASRST, F4801.WAUSER
HAVING (((F4801.WAMCU)=" 104910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 107910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 144910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 104910001") AND ((F4801.WASRST)="95"))
ORDER BY F4801.WAMCU, F4801.WADOCO;



John W. Vinson said:
Where did you put the expression? Please open the query in SQL view and post
it here.
 

Ask a Question

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.

Ask a Question

Top