Wrong values in lookup field in table? (1 Viewer)

[BJF]

Hello,

I'm having a problem getting the proper value from my calculated field in my table.
Does anyone see the problem here?

my calculated field is:

StdCost = ([R1]*[R1Percentage])+([R2]*[R2Percentage])+([R3]*[R3Percentage])

R1, R2, and R3 are all lookup fields that look to another table and each is the same, 2 columns, 1st Column is a description of the R# (column 0) and 2nd column is Cost (column 1)

In my data for this example:
R1 = 2.98 R1Percentage = 60%
R1 = 4.11 R2Percentage = 25%
R1 = 7.25 R3Percentage = 15%

My std cost based on the above calculation is returning 1.55

Yet if I add 3 more fields to the table as follows:
R1Cost
R2Cost
R3Cost
and manually type in the correct cost for each R# into R1Cost, R2Cost, and R3Cost fields by referencing what I see in the 2nd column of the dropdowns of R1, R2, and R3,
my formula (calculated field) syntax is correct which returns a correct value of 3.903
Why are my lookup fields not using the number I see from column 2 in their respective dropdowns?

Thanks for any suggestions,
BJF

 

[theDBguy]

Normally, we would recommend avoiding both calculated columns and lookup fields at the table level. I think part of your problem is the lookup values. If you set it up correctly, the value in the table is probably the ID column of the lookup table. If so, you are using the IDs in your calculation, instead of the looked up values.

 

[BJF]

Normally, we would recommend avoiding both calculated columns and lookup fields at the table level. I think part of your problem is the lookup values. If you set it up correctly, the value in the table is probably the ID column of the lookup table. If so, you are using the IDs in your calculation, instead of the looked up values.

Thanks very much for the response.
I will go o we it again and see if I can work a query into it to achieve what I am looking for. Thanks,
BJF

 

[GPGeorge]

Hello,

I'm having a problem getting the proper value from my calculated field in my table.
Does anyone see the problem here?

Spoiler: etc

my calculated field is:

StdCost = ([R1]*[R1Percentage])+([R2]*[R2Percentage])+([R3]*[R3Percentage])

R1, R2, and R3 are all lookup fields that look to another table and each is the same, 2 columns, 1st Column is a description of the R# (column 0) and 2nd column is Cost (column 1)

Spoiler: etc

In my data for this example:
R1 = 2.98 R1Percentage = 60%
R1 = 4.11 R2Percentage = 25%
R1 = 7.25 R3Percentage = 15%

My std cost based on the above calculation is returning 1.55

Yet if I add 3 more fields to the table as follows:
R1Cost
R2Cost
R3Cost
and manually type in the correct cost for each R# into R1Cost, R2Cost, and R3Cost fields by referencing what I see in the 2nd column of the dropdowns of R1, R2, and R3,

my formula (calculated field) syntax is correct which returns a correct value of 3.903
Why are my lookup fields not using the number I see from column 2 in their respective dropdowns?

Thanks for any suggestions,
BJF

I think you might have glossed over the comment about lookup fields and calculated fields.

Neither is a valuable addition to any table. Most experienced Access developers avoid them.

Using them requires deeper understanding of how they work.

Invest time in that aspect, starting with theDBGuy's suggestion.

You can eventually figure out convoluted workarounds, for sure. They will always remain both convoluted and workarounds.

 

[Pat Hartman]

Let me separate the problem.
Lookup fields in tables -- BAD
calculated values in queries -- GOOD

I don't think the OP is talking about storing the calculated value.

The Lookup fields are obfuscating the problem since you can't see what values you are actually being used in the calculation. In any event, if you want something other than the ID/text value in your query, you have to use the old fashioned method and join to the lookup table to pick up the ancillary values.

Post a picture of the relationships diagram and make sure we can see the relationships and all the relevant columns so we can work out what the SQL needs to be.

Lookup fields in tables are a crutch. They are there for rank beginners who have no clue how to create a query or write code. Once you take the first baby step into using a query or code, you start running into the confusion caused by not seeing what you are actually working with. Get rid of the lookups at the table level. Use combos on your forms. For your own use, if you need to see the text values in a query, join to the lookup table the old fashioned way.

 

[theDBguy]

I don't think the OP is talking about storing the calculated value.

Sorry, my impression was they are using a Calculated Column in the table.

 

[GPGeorge]

Perhaps this statement was misleading, then.

I'm having a problem getting the proper value from my calculated field in my table.

 

[arnelgp]

Perhaps this statement was misleading, then.

he does have a calculated column as he explicitly say so.

my formula (calculated field) syntax is correct which returns a correct value of 3.903
Why are my lookup fields not using the number I see from column 2 in their respective dropdowns?

 

[jdraw]

@BJF
I'm sensing a lot of confusion on exactly what you have and what you need. Perhaps it's time to post a copy of the database with instructions.