Large function in ms Access

Question

Hi, i am very familiar with excel's large function and i have been using it to calculate the sum of top 6 scores out of 9 scores of the 9 subjects offered during examinations. The formula used had been as follows: sum(Large(A2:A11,{1,2,3,4,5,6}). I want to achieve the same in access but it appears there's no build in Large function in excel. I believe there should be a way out but I don't know how. Please help

Answer 1

It's ironic, but mastery of Excel can initially be an impediment to success with Acccess for two reasons.

1. Excel is a flat file, which means it stores data very differently from the optimal method of storing data in a relational database such as Access. If you start out with flat file, "Excel-like" tables in Access, the result will be less than good.
2. VBA in Access and Excel are similar, but the majority of formulas in Excel have no real counterparts in Access, and vice versa. If you start out expecting to simply leverage familiarity with Excel formulas in Access, you run into dead-ends.

I've used the term "application bias" to refer to this phenomenon. By that I mean we are biased towards the familiar and therefore we can experience a new application as "wrong-headed" when it turns out not to work the same way as the one we already know. This is equally true, I suspect, for Access developers trying to learn another development tool.

Start out with a grounding in the principles of Normalization, which is the method by which properly designed relational tables are developed. And keep an open mind about coding VBA and using expressions. Many things are done for similar goals, but the specific functions and functions should not be duplicated rotely.

Answer 2

Looking forward to explore more on Access data structures and how to manipulate them

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

As its name suggests, this little demo file provides a simple introduction to designing relational databases in MS Access.  Other files in the same OneDrive folder which might be of interest to your are Normalization.zip and Relationships.zip.

As you have experience with Excel, the DecomposerDemo.zip in the same folder illustrates how data imported from Excel can be decomposed into a set of correctly normalized tables in Access.

Answer 3

Thanks for coming forward to enlighten on the problem. Much as it has you have not directly to attend to question at hand, but your insights have been a serious eye opener to me. Looking forward to explore more on Access data structures and how to manipulate them

Answer 4

Let's say you have a table tblData with a number field Score.

In a query to get the 6 largest scores:

SELECT TOP 6 Score FROM tblData ORDER BY Score DESC

To get the sum of the 6 largest scores in one query:

SELECT Sum(Score) AS TopScores FROM (SELECT TOP 6 Score FROM tblData ORDER BY Score DESC)

Answer 5

Access works very differently from Excel. You therefore cannot translate functions one-to-one from Excel to Access.

In order to provide a solution, it is important to know how the data is stored in Access.