Using a field in a formula (1 Viewer)

[stevielei]

I'm having a problem using a field ("rate") in a formula because the field is accessing another field from a table also called "rate". When I try to do say, X=Y*rate, it says "The specified field [rate] could refer to more than one table listed in the FROM clause of your SQL statement." Is there anything I can do? I don't want to change the field name.

 

[RichMorrison]

x=y*[rate].[rate]

The first "rate" identifies the table, the second identifies the field.

RichM

 

[stevielei]

Thanks for the reply, but I guess I didn't explain myself very well. I have two tables, TableA and TableB. They both have a field called "rate." I have a field in my query also called "rate" which references TableB's "rate." The only purpose right now of that field is to display the rate on the query. I have another field on the query called "Cost." In it I want the formula X=[Y]*[rate]. Right now the only way I can get it to output the right number is to put X=[Y]*[TableB].[rate]. However, I want the formula to just access the cell "rate" in the query, therefore making it X=[Y]*[rate]. If I change tables I don't want to have to change both the field "rate" in the query, and the formula Table.rate. Thank You.

 

[RichMorrison]

Hmmm...
If I understand this you have 3 "rates":
tableA.rate,
tableB.rate,
some field in the query called rate which may or may not relate to tableA or tableB.

You want X=Y*rate where rate is the query field.

If that is the case, you should rename the field in the query to "rate2" or something.
Then X+Y*[rate2] should work.

 

[stevielei]

Thanks. This idea worked by changing the "Rate" field in the query to "Cost Rate: Rate." And then making the formula "X=Y*Cost Rate" Now I only have to change what table "Cost Rate" references instead of changing the formula each time.
Thank You for your help RichMorrison.