CODEWITHSUNDEEP
sqlms-access
MarioS
MarioS

Reputation: 262

Second highest column

I have seen a similar question asked How to get second highest value among multiple columns in SQL ... however the solution won't work for Microsoft Access (Row_Number/Over Partition isn't valid in Access).

My Access query includes dozens of fields. I would like to create a new field/column that would return the second highest value of 10 specific columns that are included in the query, I will call this field "Cover". Something like this:

    Product  Bid1    Bid2   Bid3   Bid4  Cover
    Watch    104     120    115    108   115
    Shoe     65      78     79     76    18
    Hat      20      22     19     20    20

I can do a really long SWITCH formula such as the following equivalent Excel formula:

IF( AND(Bid1> Bid2, Bid1 > Bid3, Bid1 > Bid4), Bid1, 
    AND(Bid2> Bid1, Bid2 > Bid3, Bid2 > Bid4), Bid2,
    .....

But there must be a more efficient solution. A MAXIF equivalent would work perfectly if MS-Access Query had such a function.

Any ideas? Thank you in advance.

Upvotes: 1

Views: 65

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

This would be easier if the data were laid out in a more normalized way. The clue is the numbered field names.

Your data is currently organized as a Pivot (known in Access as crosstab), but can easily be Unpivoted.

This data is much easier to work with if laid in a more normalized fashion which is this case would be:

  Product   Bid   Amount  
 --------- ----- -------- 
  Watch       1      104  
  Watch       2      120  
  Watch       3      115  
  Watch       4      108  
  Shoe        1       65  
  Shoe        2       78  
  Shoe        3       79  
  Shoe        4       76  
  Hat         1       20  
  Hat         2       22  
  Hat         3       19  
  Hat         4       20

This way querying becomes simpler.

It looks like you want the maximum of the bids, grouped by Product, so:

select Product, max(amount) as maxAmount
from myTable 
group by product

Really, we shouldn't be storing text fields at all, so Product should be an ID number, with associated Product Names stored once in a separate table, instead of several times in the this one, like:

  ProdID   ProdName  
 -------- ---------- 
    1       Watch     
    2       Shoe      
    3       Hat

... but that's another lesson.

Generally speaking repeating of anything should be avoided... that's pretty much the purpose of a database... but the links below will explain than I. :)

Upvotes: 1

Related Questions