Select best 4 per subect per student

[dref]

Hi help me select the totals o the best done 4 subjects per pupil (out of the subjects some one does; the best 4) per class &Term from the results table using a query. The results table keeps results of pupils. Other tables relate to the results table. See the attached bd.Thx. Fred

[June7]

See if this helps http://allenbrowne.com/subquery-01.html#TopN

[dref]

Ok,let me change to getting the minimums. The first, second, third and forth minimum subjects. Or worst done, second worst done, third worst done....
Any help?
Thx

[orange]

See the article that June7 mentioned.
Change the sort order from Descending to Ascending.
Let us know what you find and show us what you've done.

[dref]

June7 & Orange,
Thanks but it did not find anything i could use in the link. Please look at the database i tattached and see if you cd help.
Thx again. I need new columns in a query like minimum1(Lowest),minimum2(2nd lowest),minimum3(3rd lowest) up to eight that pick data from the existing marks fields in a table.
Dref

[orange]

I looked at your database. The structure is not normalized. You should review Normalization; identify the Entities in your task;
and establish the proper relationships.

You should get some ideas for structure from this. You won't use all the tables, but some should be evident to you.
Good luck.

[June7]

I agree with Orange. The Results table is not normalized. Any analysis of the grades data will be difficult. The referenced link would be relevant for normalized data. Otherwise, a custom function could be used to determine the rankings you describe for each record.

Here is example of a function I use to determine the average of a set of values from single record (not all my data is normalized):

Function RAvg(ParamArray FieldValues()) As Variant
'----------------------------------------------------
' Function RAvg() averages all numeric arguments passed to it.
' If none of the arguments are numeric, it returns null.
' http://support.microsoft.com/kb/209839
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
   If IsNumeric(varArg) Then
      dblTotal = dblTotal + varArg
      lngCount = lngCount + 1
   End If
Next
If lngCount > 0 Then
   RAvg = dblTotal / lngCount
Else
   RAvg = Null
End If
End Function

Here is example of calling the function in a textbox:

=RAvg([tbxSPG1],[tbxSPG2],[tbxSPG3])

Another alternative is to use a UNION query to rearrange the data into a normalized structure then use that query as a source for the TOP N query demonstrated in Allen Browne article.

[dref]

Thanks Orange.
I will work on normalizing the database thou the report card format was tricky with a normalized one.
However, as a stop gap, is there a possibility of getting first minimum, second,... or maximum performances per person, displayed as different fields. I am sure there is a way. i would use say the 4 maximums to get the 4 best done subjects per pupil.
Thx

[dref]

Thx June7.
My database is small, could you kindly incorporate this and upload an attachment for me. I am sorry am being a bother on this but am abit stuck.
Where i am i am able to get a minimum /maximum per record. i want to get the second up to forth or so... such that i get the total of the best 4 or so..done subjects per person
Thx again.

[orange]

dref,

In my view you have tried to create a spreadsheet with a relational database software.
Your tables are not normalized - and you will have all sorts of issues with inserts, deletes and retrieval. This could be prevented by normalizing your tables, at least enough to extract the information for which the database was built.
In addition, you are using Lookups at the table field level which many developers would tell you is a no-no.
See this for more info.

Since the database is small, you may want to redesign; normalize your tables; set up appropriate relationships; test the model with sample data; then build your queries etc.

Once you get a design that works, you can move your data with a series of queries and possibly some vba to populate the new structure.

I don't know your background or intentions, but I do see you have 43 posts, so this isn't your first database activity.
If you are going to continue with database and Access, I strongly suggest you go through some basic learning of design.
If you keep going with your current approach, you would be better off using Excel or spreadsheet software.

People here will help you and offer advice and recommendations. You don't have to accept these. But you are asking people to disregard their experience and expertise and solve a problem that could have been corrected by using basic design practices.

Good luck with your project.

This tutorial will help you with the concepts and steps involved.

[June7]

Here is an example of code to find the maximum value in a set of values http://answers.microsoft.com/en-us/o...0-7918b7068b5f

It could be adapted to return whichever ranked value you want.

If you want to stick with this structure and use the suggested VBA, then adapt the code. I will advise but don't want to do your work. Size of database is irrelevant to effort required for programming task.