Arithmetic not correct when lookups are part of expression

[CTdbdev]

Hello,

Running into an error here I'm not quite figuring out and can't find anything on this subject. In my table of products, in particular, I have a column for it's product code, vendor cost, product option lookup cells (which just reference another selected product and it's vendor cost), and a total cost column. In the total cost column I have an expression for example of =([vendcost] + [prodopt1] + [prodopt2]).

The problem is, I can't get it to use the cost of the selected product option cells, but rather it always defaults to use the ID number in the math. I've moved the columns around so the cost column was the far left in the lookup cells, but that doesn't work. Ideally the value shown would be the product code, but it's cost would be used for the math. So now that I'm writing this I'm thinking if that could be written into the expression that would correct the error? Or am I tackling this the wrong way?

Regards,
Alex

[orange]

It appears that

a) you have not normalized your table(s)
b) are including calculations/totals in your table. Better to do these via query when needed.

Suggest you tell us what the database is about in plain English.

[CTdbdev]

The thing is, I don't have multiple tables for this function. Everything is happening within just the one products table. I tried a new approach. In the product page form, I added a combo box that's referencing ID, Product code, and vendor cost. The selected value from the combo box is then stored in table column named prodopt1,2,etc. I edited the combo box so the vendor cost column is on the far left, since only the far left value is what only ever gets stored in the record cell. Now back in the table, it's only the vendor cost displayed, so the total cost column now adds right. Yes, I'm having math done and stored in the table this way. Is this solution described not the way to do things?

[John_G]

Hi -

I suspect you have encountered one of the pitfalls of using lookups in tables. The consensus amongst the experts is that they are an abomination, to be avoided for reasons you have found.

My suggestion would be to replace those with numeric pointers to the other records, and then use queries to calculate and display results.

John

[Missinglinq]

...The consensus amongst the experts is that they are an abomination, to be avoided...

And here's why:

http://mvps.org/access/lookupfields.htm

Linq ;0)>

[John_G]

Re: post #3

You don't need multiple tables. A query can contain the same table more than once - it adds _1, _2.... to the tables names, and you can create the joins as you would with any two tables. You do have to be a bit careful about which occurance you are referring to in expressions.

As to keeping calculated results in the tables, you really shouldn't - that's what queries are for.

HTH

John

[CTdbdev]

Ok, I reworked things steering away from lookups. I made a query that lists the products and their cost. In the product report there are a couple combo boxes that reference that query. In the combo box wizard I selected to save the costs to the applicable columns in the original table for each product. That's all working correctly. If I'm executing this correctly from the feedback today...

From my research, I saw that you don't need to/or shouldn't include the ID in the query. Is this correct? I only have the products and their cost in the query. It seems each product is remembering which item I selected as when I go back to a particular item, the combo boxes do display the correct values.

Lastly, so if I shouldn't store calculated results in a table, and should by query, is another equally proper way to just have a non editable text box in my report and write an expression that does the calculation control it?

Regards,
Alex

[June7]

Whether or not ID should be included in combobox query depends on whether or not the ID is saved as foreign key. If it is not saved as FK then it doesn't need to exist at all.

Calc in query usually preferable but calc in textbox acceptable.

[CTdbdev]

Great! Thanks!