- 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?
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?