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:

SWITCH

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.

MAXIF

Any ideas? Thank you in advance.

1 Answer
1

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

Quackit : Microsoft Access Tutorial

YouTube : DB Planning

Microsoft : Database Design Basics

Microsoft : Database Normalization Basics

Wikipedia : Database Normalization

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.