Apr 18 2012, 10:27 AM
My table m_products with files Id, desc, price1, price2, price3 and priceMax.
I want to make an update query to choose maximum price value between price1, price2 and price3 of the same record and update the value in priceMax.
Apr 18 2012, 10:34 AM
Anytime I see "price 1, price 2, price 3, etcc" all in one table I immediately suspect table normalization problems.
And most experienced posters here at UA will tell you that while you can "cover up" most table structure problems by throwing enough code at them,
it is rarely worth the effort, and with time the problems with the application just get progressively harder to fix via code (and said code usually gets pretty messy, as well).
It is almost always much better to correct table structure problems first.
So, since UA does not promote poor table design,
I am reluctant to try providing an answer without first suggesting that you check your table structure for normalization.
Some good reference posts for Normalization (in no particular order) are:
Please excuse me if I've misunderstood your problem, and if so, maybe a little more detail about your table structure would help.
Apr 18 2012, 10:36 AM
Pardon me but that table structure is considered a "bad design" for a database. It looks more like a spreadsheet. If you just have the fields: ID, Desc, and Price; then it would be easy to "calculate" the max value for the Price field any time you need it, and there won't be any need to store max value in the table.
Take a look at this Wiki article to see what I mean: Normalization
Just my 2 cents...
Apr 19 2012, 09:16 AM
Thanks theDBguy and rbianco for yours answers but were not useful, but was my fault.
I change the field names to be more easy to understand and switch the discussion about the structure and not the way to resolve my needs.
The system throw me three type of analysis per each sample and are thousands of samples. And each sample is unique.
I need to resolve the maximum value by each sample. Best if I can store in the same table.
and there is no more PriceN.
Sorry I though was simple and then need help.
Apr 19 2012, 09:54 AM
UPDATE m_products SET priceMax = IIF( price1 > price2, IIF( price1 > price3, price1, IIF( price2 > price3, price2, price3)), price2)
Apr 19 2012, 02:26 PM
Thank a lot, that is the simple I supposed.
Apr 19 2012, 02:32 PM
And work perfect, of course the final line is like this:
UPDATE m_product SET m_product.pricemax = IIf([m_product].[price1]>[m_product].[price2],IIf([m_product].[price1]>[m_product].[price3],[m_product].[price1],IIf([m_product].[price2]>[m_product].[price3],[m_product].[price2],[m_product].[price3])),[m_product].
some changes for my chemical analysis spreadsheet and ready to reduce my work
Apr 19 2012, 02:40 PM
Glad to hear you were able to get the answer you were looking for.
We are all happy to help. Good luck with your project.
Nice work, Arnel.