Best 4 out of 6

[Bill]

I need to calculate total points per individuals using only their best 4 out
of 6 points earned. My table fields are as follows

Name
Points1
Points2
Points3
Points4
Points5
Points6
TotalPoints

Can you help me accomplish this? I know in Excel I could use the small
function and drop the lowest number.

 

[Tom van Stiphout]

On Fri, 24 Jul 2009 05:09:01 -0700, Bill

One reason this is so complicated for you is that you have an
incorrect database design. You have what's called a Repeating Group
and that's a big no-no in a relational database. Consider a design
like this:
PersonID long int FK PK
TestID long int FK PK
Points

You also have a Calculated Field in TotalPoints, another no-no. Or at
least you should really know what you're doing before you decide on
such field. Rather this value should be calculated on the fly, in a
query.

-Tom.
Microsoft Access MVP

 

[Bill]

Tom, in your example is PersonID my table field called Name? What is TestID?
Probably dumb questions but I don't understand. Points I got. I do not
have to store Total Points, it can be generated each time I run the query.
Another thought, my table would not have a primary key correct? (now it is
Name)

 

[Tom van Stiphout]

On Fri, 24 Jul 2009 06:54:01 -0700, Bill

I'm guessing Name is the name of the person taking the test. In my
design there would be a Persons table, with PersonID PK autonumber,
PersonFirstName, etc. Then I assume you'll have more than one test, so
I'll design a table with all kinds of tests: TestID PK autonumber,
TestName text, etc. Then we realize that there is a many-to-many
relation between these two tables: each person takes many tests, and
each test is taken by many people. So the TestResults table is what I
laid out. It has a compound primary key over the combination of
PersonID and TestID, and for each such combination there are Points.

-Tom.
Microsoft Access MVP

 

[Bill]

John, that worked. One other step though. I tried to modify the queries but
I was unsuccessful. I need to sort the results by points within agegroup
within series.

I added 'series as theseries' and 'agegroup as theagegroup' to the first
query, just after 'name as thename'. Thta worked OK.

In the second query I added - (Select TOP 4 points, theseries, theagegroup
FROM theunionquery as TEMP) where etc.............

That query did not work. Any ideas on how to fix that?
--
Thank you for your help.

Use a union query to normalize the data.
First Query - Saved as TheUnionQuery
SELECT [Name] as TheName , Points1 as Points
FROM SomeTable
UNION ALL
SELECT [Name], Points2 as Points
FROM SomeTable
UNION ALL
SELECT [Name], Points3 as Points
FROM SomeTable
UNION ALL
SELECT [Name], Points4 as Points
FROM SomeTable
UNION ALL
SELECT [Name], Points5 as Points
FROM SomeTable
UNION ALL
SELECT [Name], Points6 as Points
FROM SomeTable

Second query - uses the first to get the totals. The problem is that if there
are ties for the 4th points then the ties will be included.
SELECT TheName, SUM(Points) as TotalPoints
FROM TheUnionQuery
WHERE Points in
(SELECT TOP 4 Points
FROM TheUnionQuery as TEMP
WHERE Temp.TheName = TheUnionQuery.TheName
ORDER BY Points DESC)
GROUP BY TheName

Another option would be to write a custom VBA function to step through the
values, pick out the highest 4, sum them, and then return the result.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I need to calculate total points per individuals using only their best 4 out
of 6 points earned. My table fields are as follows

Name
Points1
Points2
Points3
Points4
Points5
Points6
TotalPoints

Can you help me accomplish this? I know in Excel I could use the small
function and drop the lowest number.

 

[John Spencer]

SELECT TheName
, TheSeries
, TheAgeGroup
,SUM(Points) as TotalPoints
FROM TheUnionQuery
WHERE Points in
(SELECT TOP 4 Points
FROM TheUnionQuery as TEMP
WHERE Temp.TheName = TheUnionQuery.TheName
ORDER BY Points DESC)
GROUP BY TheName
, TheSeries
, TheAgeGroup

You may need to modify the sub-query in the where clause to
(SELECT TOP 4 Points
FROM TheUnionQuery as TEMP
WHERE Temp.TheName = TheUnionQuery.TheName
And Temp.TheSeries = TheUnionQuery.TheSeries
And Temp.TheAgeGroup = TheUnionQuery.TheAgeGroup
ORDER BY Points DESC)

You would need that modification if your scores were dependent on the
AgeGroup and the Series. In other words, you need it if a Name can have
more than one set of points depending - say one set of scores for each
series that a name participates in. The AgeGroup may or may not be
needed again if a name and series can have a set of scores for more than
one age group then you need to include that also in the sub-query criteria.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================