Query Help

[KimmyG]

Hi, all. I'm new here, but have been working with Access and SQL for too many years to count. I've developed a small database on a pro bono basis for our local 4H chapter. They use it to track scores for the horse program and qualifications for state fair.

This year, they are have a new event. I'm trying to figure out the scoring for this and, admit, I'm stumped. Here's the scenario. The event has 6 categories. Qualifications are determined by the top 5 scores (lowest score is dropped), then added then averaged. All scores for the individual are in a single record/table. Example below would be one record in a table. There would be a record for each individual participating.

Name Number SMS WR Trail Cows Roping Knowledge
Kim 100 88 91 83 87 86 89

In this example, the lowest score, 83, would be dropped. The remaining scores would be added together, then divided by 5 to obtain an average score.

What I'm struggling with is how to drop this score in a single record. I don't want to have to create 6 different tables to accomplish this...one for each category.

Any thoughts? Thank you so much!

 

[Minty]

If you store the data as

Name Number SkillType Result

This becomes easy.

 

[bastanu]

Add the following two functions to a standard module:

Function Minimum(ParamArray FieldArray() As Variant)
   ' Declare the two local variables.
   Dim I As Integer
   Dim currentVal As Variant

   ' Set the variable currentVal equal to the array of values.
   currentVal = FieldArray(0)

   ' Cycle through each value from the row to find the smallest.
   For I = 0 To UBound(FieldArray)
      If FieldArray(I) < currentVal  Then
         currentVal = FieldArray(I)
      End If
   Next I

   ' Return the minimum value found.
   Minimum = currentVal

End Function

Function Maximum(ParamArray FieldArray() As Variant)
   ' Declare the two local variables.
   Dim I As Integer
   Dim currentVal As Variant

   ' Set the variable currentVal equal to the array of values.
   currentVal = FieldArray(0)

   ' Cycle through each value from the row to find the largest.

   For I = 0 To UBound(FieldArray)
      If FieldArray(I) > currentVal Then
         currentVal = FieldArray(I)
      End If
   Next I

   ' Return the maximum value found.
   Maximum = currentVal

End Function

Now you can create a query and use:

AVG: (([SMS]+[WR]+[Trail]+[Cows]+[Roping]+[Knowledge])-Minimum([SMS],[WR],[Trail],[Cows],[Roping],[Knowledge]))/5

Cheers,
Vlad

 

[plog]

To further what Minty said--you are not storing this data properly. Data should be accomodated vertically (with more rows) and not horizontally (with more columns). Those 7 scores should be 7 records in a table that Minty described.

Additionally, I think your count is off. I see 7 numeric values in your data.

ExistingTable
Name Number SMS WR Trail Cows Roping Knowledge
Kim 100 88 91 83 87 86 89

ScoreTable
MemberName, SkillType, SkillScore
Kim, 100, SMS
Kim, 88, WR
Kim, 91, Trail
Kim, 83, Cows
...


Then to get Kim's final score per your specifications, you then can do it with just a few lines of SQL which involve a subquery:

SELECT MemberName, SUM([SkillScore]) AS GrossScore, MIN([SkillScore]) AS LowestScore, COUNT([SkillScore]) AS TotalSkills
FROM ScoreTable
GROUP BY MemberName;

Name the above query as 'subScores' then to arrive at what you want this SQL will do it:

SELECT MemberName, (GrossScore - LowestScore)/ (TotalSkills-1) AS NetScore
FROM subScores;

The beauty of this solution is that next year when 'Butchering' and 'Barbecuing' become skills, you simply add data and don't touch any code to accomodate them.

 

[KimmyG]

Thanks, all. I will play with this more. The "number" is participant number, not a scored value. So there are only six sets of scores.

 

[Pat Hartman]

Think of it this way, if there is a 7th event next year you won't have to modify the app again. Once the event scores are properly normalized, it doesn't matter how many events there are or how many a person participates in.