Across fields max value for record?

[HeRoze]

Greetings,

Is there a practical method to find the max value of several fields in each record? The actual table is quite large, so I'll have to go with a sample....

Item# Price1 Price2 Price3 Price4 PriceMAX
A1401 $10.41 $12.99 $11.05 $18.76 ?<-------(would be $18.76)
A1798 $14.01 $17.79 $12.65 $19.65 ?
A2875 $21.40 $32.56 $28.45 $19.95 ?

Unlike the MAX() function as I understand it, I am trying to find the maximum value for each item #. So, for Item# A1401 PriceMAX=$18.76.

I've done this before with IIF in a calculated field, but now the fields to look at are expanding and the formula is getting cumbersome (but doable).

Thanks for any help or ideas!
-=-=-=-=-=-
A little background: I can muddle my way through SQL, but primarily use the functionality in the query builder interface. My actual database is about 170,000 items and I need to find aggregate dates, prices, and quantities. I can port to excel (as I usually do), but there has to be a way in Access, right? If I can figure out how to do it for the maximum I'll have a good chance at finding averages, minimums, or other calculated values. Using Access 2010.

Regards, Vic

 

[pbaldy]

Your table isn't normalized, but:

https://support.microsoft.com/en-us/kb/209857

 

[HeRoze]

Not normalized? Not sure what you mean since all values are unique....

I'll check out the link at work. I googled and that never popped up, so I appreciate the link!

 

[pbaldy]

Your fields of price1, price2 would normally be stored as records, not fields:

http://www.r937.com/Relational.html

With that design, a simple totals query gives you the max price per item.

 

[HeRoze]

Ah, gotcha - thanks again. For this particular case each of the price fields comes from a different database, which are linked to the part#. Completely different databases that are controlled by different entities. I have to use a web query to gather the info from the multiple sources and compile into something useable.

 

[spikepl]

So you do not need to produce unnormalized data but can easily save each price in a separate record.

 

[pbaldy]

I'd bring it in like

Item...Price...Source

or whatever is appropriate.

 

[HeRoze]

pbaldy - spikepl,

Thanks! I never thought about puling in the data like that. I forced the part# to have no duplicates. Thinking about your suggestion on the structure, the source file records each have a date field as well that would further delineate.

I cooked the books using Excel before bringing in to Access, and now I wonder if I would have just globbed everything together up front could Access have handled the whole thing.

 

[pbaldy]

Happy to help!