Using a field reference in a query critereon

Joined
Aug 9, 2009
Messages
11
Reaction score
0
I need to know how to use a table-field reference in the criteria for a query result.

I want to see all records where field 1 is different than field 2 by a certain variance amount. I do NOT want to enter this amount as a number each time the query is run via a parameter query for reasons not relevant to my request for help. I want the variance amount to come from a field in a table.

For the simplified example, lets say that I want to know all instances where amount 1 is more than amount2 by more than 5%.

First I enter .05 in the Variance Field of the Constants table.

Then I want a "column" in the query that is headed:
AmountDiff: ([table1].[amount1]-[table2].[amount2])/[table2].[amount2])

This will get me the different between the two amounts as a percentage of Amount2.

Then in the criteria, I want to enter the value of the Variance field in the Constants table. But if I put
>[Constants].[Variance]
in the criteria row, Access thinks that I am asking for user input.

How to I put that reference in the criteria row?
 
Back
Top