VBA CODE TO SUM 5 FIELDS WITH LOWER VALUES IN ACCESS DATABASE

[INCOGNITO MAYDAY]

---

STUDENT NAME​	MATH​	BIOLOGY​	PHYSICS​	CHEMISTRY​	GEOGRAPHY​	HISTORY​	LITERATURE​	BOTT_5_SUM​
D TRUMP​	96​	85​	87​	91​	85​	75​	65​	397​
J BIDEN​	72​	87​	94​	77​	75​	95​	63​	374​
K HARRIS​	72​	75​	69​	85​	73​	81​	90​	370​

So I have that access form and I want to put a piece of VBA code behind the form that will select 5 with lower values and sum them to get a value in (BOTT_5_SUM) field

Simply the value in the last field (BOTT_5_SUM) is the SUM of values from 5 fields with lower values

In every row I have marked the 5 lowest values with red ink, Now I need a VBA code to sum them in the LAST FIELD (BOTT_5_SUM)

Help me please, its urgent

 

[Minty]

Welcome to AWF.
Two things - is this a class assignment?
And show us your table structure, because if it's laid out as above you have much more serious problems with the way your data is stored.

 

[CJ_London]

can probably be done in sql (using the TOP 5 predicate and sort values desc) rather than VBA, but does require normalised tables

Other methods include using wizhook to sort an array, then sum the first 5 elements

But no point in going into detail until we know the actual data structure and potential other issues - such as is this a report type form (i.e. data is not editable) can there be more or less subjects than those shown? what if D trump does not do literature so has zero points/marks? Does the zero count as one of the five?

 

[INCOGNITO MAYDAY]

can probably be done in sql (using the TOP 5 predicate and sort values desc) rather than VBA, but does require normalised tables

Other methods include using wizhook to sort an array, then sum the first 5 elements

But no point in going into detail until we know the actual data structure and potential other issues - such as is this a report type form (i.e. data is not editable) can there be more or less subjects than those shown? what if D trump does not do literature so has zero points/marks? Does the zero count as one of the five?

Yes its a report type of form that fetchs data from a query and that query fetchs data from different tables..

Given every student takes every subject listed above

 

[INCOGNITO MAYDAY]

Welcome to AWF.
Two things - is this a class assignment?
And show us your table structure, because if it's laid out as above you have much more serious problems with the way your data is stored.

Thats a form that fetch data from a query so it looks exactly as you see it

 

[arnelgp]

see Query1 and the function in Module1.
note that the code is somewhat "hard-coded".
also note, you need to Normalize your table.

 

[MajP]

You can normalize that with a UNION query and then sum top N per group. In this format easier to work with.

SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Math] AS Score, "Math" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Biology] AS Score, "Biology" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Physics] AS Score, "Physics" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Chemistry] AS Score, "Chemistry" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Geography] AS Score, "Geography" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[History] AS Score, "History" AS Class
FROM YourQuery
UNION
SELECT YourQuery.ID, YourQuery.[STUDENT NAME], YourQuery.[Literature] AS Score, "Literature" AS Class
FROM YourQuery
order by 2,3 desc

However, I need help myself on the Top N per group where you have ties. I wrote the top n per group but it included each tie occurence.

 

[Pat Hartman]

Given every student takes every subject listed above

That is the hallmark of a class assignment rather than a real world scenario. We're happy to help with class assignments but we do it differently.

 

[MajP]

I think this works you need to verify and check Ties.
qryNormal is above
qryTop5PerGroup

SELECT A.ID, A.[STUDENT NAME], A.Score, A.Class
FROM qryNormal AS A
WHERE (((A.Class) In
(SELECT TOP 5 B.Class FROM qryNormal AS B WHERE A.ID = B.ID ORDER BY B.Score DESC , B.Class)));

qrySumTop5

SELECT qryTop5PerGroup.ID, Sum(qryTop5PerGroup.Score) AS SumOfScore
FROM qryTop5PerGroup
GROUP BY qryTop5PerGroup.ID;

Then use it in final

SELECT yourquery.[student name],
       yourquery.math,
       yourquery.biology,
       yourquery.physics,
       yourquery.chemistry,
       yourquery.geography,
       yourquery.history,
       yourquery.literature,
       qrysumtop5.sumofscore
FROM   yourquery
       INNER JOIN qrysumtop5
               ON yourquery.id = qrysumtop5.id;

Some of this can be combined, but easier to see this way.

FYI. I noticed I did the 5 highest not lowest. Remove the DESC in the sort