DMin Built-in function in a Query

[Tami]

I have a database in Access that I use for my Science Fair students. They
are scored four times, but, I only want the three highest scores. So, I
thought I could use DMIN to find the lowest score, and then I would subtract
that score from my totals score I created in the query. Can I use DMin
function in a query for this, or will DMin only work with groups of records?
The four fields are [S1] [S2] [S3] [S4]. In the query, I typed:
LowestScore: DMin(database, field, criteria). DMin([Science Fair]![total],
[Science Fair]![S1], [Science Fair]![S2], [Science Fair]![S3],Science
Fair]![Science Fair]![S4], criteria) I'm stumped. If you have an easier
better solution, I'm all for it as I have put hours into this going in
circles. Thanks!

 

[Ken Snell \(MVP\)]

Your problem is because your table is not normalized. If you had a single
table for the students' scores, with one record for each student's score,
you could easily write a query that would find the top 3 grades for each
student.

Because you are using separate fields, you will need a 'chain' of queries.

The first one is a union query to normalize the data. Create and save this
query (name it qryUnionGrades):

SELECT StudentID, S1 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S2 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S3 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S4 AS StudentGrade
FROM Students;


Then use a query like this to get the sum of the scores for each student -
name it qryGradesSummed:

SELECT qryUnionGrades.StudentID,
SUM(qryUnionGrades.StudentGrade) AS SummedGrades
FROM qryUnionGrades
GROUP BY qryUnionGrades.StudentID;


Then you can use this to subtract the lowest grade and get the sum of the
three highest grades for each student:

SELECT qryGradesSummed.StudentID,
qryGradesSummed.SummedGrades -
Nz(DMin("StudentGrade", "qryUnionGrades", "StudentID = " &
qryGradesSummed.StudentID),0)
AS SumOfTop3Grades
FROM qryGradesSummed;

 

[Ken Sheridan]

As Ken Snell has explained the problem stems from a poor design. You can
write a function to do what you want however. Here's a couple, for getting
the Min and Max values from a set of values, which you can paste into a
standard module in your database:

Public Function GetMin(ParamArray aVals() As Variant) As Double

' returns lowest value from list of values

Dim dblMin As Double
Dim var As Variant

' first set dblMin variable to max possible value
dblMin = 1.79769313486231E+308

' loop through array of values and compare each
' with value of dblMin variable.
' If lower then assign current value to the variable
For Each var In aVals
If var < dblMin Then
dblMin = var
End If
Next var

GetMin = dblMin

End Function

Public Function GetMax(ParamArray aVals() As Variant) As Double

' returns highest value from list of values

Dim dblMax As Double
Dim var As Variant

' first set dblMax variable zero
dblMax = 0

' loop through array of values and compare each
' with value of dblMax variable.
' If higher then assign current value to the variable
For Each var In aVals
If var > dblMax Then
dblMax = var
End If
Next var

GetMax = dblMax

End Function

In your query, based on the table containing the scores, to get the lowest
score you'd put the following in the 'field' row of a blank column in design
view:

LowestScore: GetMin([S1], [S2], [S3], [S4])

To get the sum of the top three scores for each student, assuming everyone
has all four scores:

TopThreeScores: ([S1]+[S2]+[S3]+[S4]) - GetMin([S1], [S2], [S3], [S4])

Ken Sheridan
Stafford, England

 

[Tami]

I'd like to normalize this table. Can you walk me through this? My
experience has been: Working with one table creating simple, forms, queries
and reports with very little experience with building a relationship. First
I need to add a table called, Student Scores with a common field
"STUDENTsfID" and a field "Score" When you said one record for each of the
student's score, I'm guessing there should only be one score field. How am I
doing so far?
--
Thank you,

Tami

Your problem is because your table is not normalized. If you had a single
table for the students' scores, with one record for each student's score,
you could easily write a query that would find the top 3 grades for each
student.

Because you are using separate fields, you will need a 'chain' of queries.

The first one is a union query to normalize the data. Create and save this
query (name it qryUnionGrades):

SELECT StudentID, S1 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S2 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S3 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S4 AS StudentGrade
FROM Students;


Then use a query like this to get the sum of the scores for each student -
name it qryGradesSummed:

SELECT qryUnionGrades.StudentID,
SUM(qryUnionGrades.StudentGrade) AS SummedGrades
FROM qryUnionGrades
GROUP BY qryUnionGrades.StudentID;


Then you can use this to subtract the lowest grade and get the sum of the
three highest grades for each student:

SELECT qryGradesSummed.StudentID,
qryGradesSummed.SummedGrades -
Nz(DMin("StudentGrade", "qryUnionGrades", "StudentID = " &
qryGradesSummed.StudentID),0)
AS SumOfTop3Grades
FROM qryGradesSummed;


--

Ken Snell
<MS ACCESS MVP>

I have a database in Access that I use for my Science Fair students. They
are scored four times, but, I only want the three highest scores. So, I
thought I could use DMIN to find the lowest score, and then I would
subtract
that score from my totals score I created in the query. Can I use DMin
function in a query for this, or will DMin only work with groups of
records?
The four fields are [S1] [S2] [S3] [S4]. In the query, I typed:
LowestScore: DMin(database, field, criteria). DMin([Science
Fair]![total],
[Science Fair]![S1], [Science Fair]![S2], [Science Fair]![S3],Science
Fair]![Science Fair]![S4], criteria) I'm stumped. If you have an easier
better solution, I'm all for it as I have put hours into this going in
circles. Thanks!

 

[Tami]

I'd like to normalize it. So, I created a second table, StudentScore with
two fields, StudentSFid (for the relationship) and 2nd field, Score. You
mentioned one record for each score, so, I only put that one score field. Is
this correct so far?

With a new table, each time you enter a score, you have to, one enter the
StudentSFid, and then the score and you would have tor repeat this four times.

Now I should be ready to easily have a query find the lowest score and drop
that number and total the three highest scores. How would I do this?
--

Thank you,

Tami

Your problem is because your table is not normalized. If you had a single
table for the students' scores, with one record for each student's score,
you could easily write a query that would find the top 3 grades for each
student.

Because you are using separate fields, you will need a 'chain' of queries.

The first one is a union query to normalize the data. Create and save this
query (name it qryUnionGrades):

SELECT StudentID, S1 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S2 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S3 AS StudentGrade
FROM Students
UNION ALL
SELECT StudentID, S4 AS StudentGrade
FROM Students;


Then use a query like this to get the sum of the scores for each student -
name it qryGradesSummed:

SELECT qryUnionGrades.StudentID,
SUM(qryUnionGrades.StudentGrade) AS SummedGrades
FROM qryUnionGrades
GROUP BY qryUnionGrades.StudentID;


Then you can use this to subtract the lowest grade and get the sum of the
three highest grades for each student:

SELECT qryGradesSummed.StudentID,
qryGradesSummed.SummedGrades -
Nz(DMin("StudentGrade", "qryUnionGrades", "StudentID = " &
qryGradesSummed.StudentID),0)
AS SumOfTop3Grades
FROM qryGradesSummed;


--

Ken Snell
<MS ACCESS MVP>

I have a database in Access that I use for my Science Fair students. They
are scored four times, but, I only want the three highest scores. So, I
thought I could use DMIN to find the lowest score, and then I would
subtract
that score from my totals score I created in the query. Can I use DMin
function in a query for this, or will DMin only work with groups of
records?
The four fields are [S1] [S2] [S3] [S4]. In the query, I typed:
LowestScore: DMin(database, field, criteria). DMin([Science
Fair]![total],
[Science Fair]![S1], [Science Fair]![S2], [Science Fair]![S3],Science
Fair]![Science Fair]![S4], criteria) I'm stumped. If you have an easier
better solution, I'm all for it as I have put hours into this going in
circles. Thanks!

 

[John Spencer]

The basic query would look like this

SELECT StudentSFid
, SUM(Score) - Min(Score) as TheScore
FROM StudentScore
GROUP BY StudentSFid

YOu will probably want to show more information on the student such as last
name. SO that query would be modified slightly to include the Student table.

SELECT Student.LastName, Student.FirstName,
StudentScore.StudentSFid
, SUM(Score) - Min(Score) as TheScore
FROM StudentScore INNER JOIN Student
On StudentScore.StudentSFid = Student.StudentSFid
GROUP BY StudentScore.StudentSFid, Student.LastName, Student.FirstName

If you don't understand building a query in the SQL window. Post back and we
should be able to lead you through the process using the query design view.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Snell \(MVP\)]

I apologize that I was unable to reply to your posts over past few days --
work called on my time.

Thanks, John, for stepping in!

 

[Tami]

Query design view would be best. Here are the file names I have used in
Table ScienceFair, field names: FirstName LastName In Table
StudentScores, field names ProjectNo Score

I've never used SQL window as it wasn't covered in the on-line classes I took.

I would like to see the Student Names, Project No. and final score.

Thank you for your patience. Seeing the capability of typing in SQL makes
me feel pretty inexperienced.

-
Thanks again,

Tami

 

[Bob Barrows [MVP]]

Actually, just paste the suggested sql statement into the sql window and
switch back to the Design View to see how you should have done it in the
grid.

 

[John Spencer]

In a new query
-- Add both tables
-- Drag from ScienceFair.StudentSFID to StudentScores.SFID to establish the join
-- Add the fields StudentScores.StudentSfID, LastName, Firstname, and
ProjectNo to the query
-- SELECT View: Totals from the menu
-- Type the following into a blank field "block"
TheScore: SUM(Score) - Min(Score)
-- Set the Total "block" to Expression

If you switch to the SQL view that should look something like

SELECT StudentScores.StudentSFid, ScienceFair.LastName
, ScienceFair.FirstName, StudentScores.ProjectNo
, SUM(Score) - Min(Score) as TheScore
FROM StudentScores INNER JOIN ScienceFair
On StudentScores.StudentSFid = ScienceFair.StudentSFid
GROUP BY StudentScores.StudentSFid, ScienceFair.LastName
, ScienceFair.FirstName, StudentScores.ProjectNo

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

 

[Tami]

I'm confused on the beginning of the program
StudentScores.StudentSFid
I put the above in originally but received errors. I'm not sure I'm
understanding what the are. Is it the name of the Table which holds the
scores and the ID name from the same Table or the other Table (ScienceFair)?
My tables are:
StudentScores is my 2nd Table with ScoreID
ScienceFair is my 1st Table with STUDENTsfID

This is what I'm getting in my query and the program part is under it:

LastName FirstName ProjectNo Score TheScore
DelSignore Joslyn 4 75 0
DelSignore Joslyn 4 85 0
DelSignore Joslyn 4 90 0
DelSignore Joslyn 4 100 0
Doherty Elyse 2 50 0
Doherty Elyse 2 80 0
Doherty Elyse 2 87 0
Doherty Elyse 2 99 0
Elia Victoria 1 75 0
Elia Victoria 1 80 0
Elia Victoria 1 90 0
Elia Victoria 1 98 0
Shore Robert 3 85 0
Shore Robert 3 90 0
Shore Robert 3 100 100


SELECT ScienceFair.LastName, ScienceFair.FirstName, StudentScores.ProjectNo,
StudentScores.Score, Sum(Score)-Min(Score) AS TheScore
FROM ScienceFair INNER JOIN StudentScores ON ScienceFair.ProjectNo =
StudentScores.ProjectNo
GROUP BY ScienceFair.LastName, ScienceFair.FirstName,
StudentScores.ProjectNo, StudentScores.Score;

What I'd like is for it to drop the lowest of the four scores and add the
highest three scores giving me a final score. Am I grouping on two many
fields so that it doesn't know that each student is it's own group with four
scores? I'd prefer to use the Project No as the group on as that field ties
the two Tables together and some students have the same last name.

Thank you for your time and patience. --
Thank you,

Tami

 

[Tami]

Please look at the results below. It's not working It's not finding the
lowest price per student and then adding the three highest scores and putting
it in the total.


StudentScores is my 2nd Table with ScoreID
ScienceFair is my 1st Table with STUDENTsfID

This is what I'm getting in my query and the program part is under it:

LastName FirstName ProjectNo Score TheScore
DelSignore Joslyn 4 75 0
DelSignore Joslyn 4 85 0
DelSignore Joslyn 4 90 0
DelSignore Joslyn 4 100 0
Doherty Elyse 2 50 0
Doherty Elyse 2 80 0
Doherty Elyse 2 87 0
Doherty Elyse 2 99 0
Elia Victoria 1 75 0
Elia Victoria 1 80 0
Elia Victoria 1 90 0
Elia Victoria 1 98 0
Shore Robert 3 85 0
Shore Robert 3 90 0
Shore Robert 3 100 100


SELECT ScienceFair.LastName, ScienceFair.FirstName, StudentScores.ProjectNo,
StudentScores.Score, Sum(Score)-Min(Score) AS TheScore
FROM ScienceFair INNER JOIN StudentScores ON ScienceFair.ProjectNo =
StudentScores.ProjectNo
GROUP BY ScienceFair.LastName, ScienceFair.FirstName,
StudentScores.ProjectNo, StudentScores.Score;

What I'd like is for it to drop the lowest of the four scores and add the
highest three scores giving me a final score. Am I grouping on two many
fields so that it doesn't know that each student is it's own group with four
scores? I'd prefer to use the Project No as the group on as that field ties
the two Tables together and some students have the same last name.

Thank you for your time and patience. --
Thank you,

--
Thank you,

Tami