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.
Thanks.

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:

CODE

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• A Discussion on Storing Calculated Values and Audit Trails
• Jerry's FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS BASICS
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables
• Sharing/Splitting a Database

Please excuse me if I've misunderstood your problem, and if so, maybe a little more detail about your table structure would help.

Hi,



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

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.

So; Product-Sample
Price1-Analisis1
Price2-Analisis2
Price3-Analisis3
and there is no more PriceN.

Sorry I though was simple and then need help.

Thank a lot, that is the simple I supposed.

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].
[price2]);

some changes for my chemical analysis spreadsheet and ready to reduce my work

thanks again

Hi,



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.