Select & Max Function Q

S

stvlai

Hi

How are you guys doing? I have a question about using SELECT and MAX
function. Say I have a Students Table with Fields StudentID,
FirstName, MiddleName, Score

StudentID FirstName MiddleName Score
1 John Doe 10
2 Mary Jane 20
3 Peter Smith 18
4 John Doe 30 ......
John's high score
5 Mary Jane 22
6 Peter Smith 30
7 John Doe 19
8 Mary Jane 25 ......
Mary's high score
9 Peter Smith 33 ......
Peter's high score

I use a Max statement to extract out each Student Max Score e.g
SELECT DISTINCTROW Max(Students.Number) AS [High Score],
Students.FirstName
FROM Students GROUP BY Students.FirstName;
Correct Results : John 30 Mary 25 Peter 33

Since I also need their Middle Name, I added their MiddleName field
too. BUT it return all the Rows (9 Records), which is NOT correct. e.g

SELECT DISTINCTROW Max(Students.Number) AS [Max Of Number],
Students.FirstName, Students.MiddleName FROM Students GROUP BY
Students.FirstName, Students.MiddleName;

How can I select each Student Score with their FirstName and
MiddleName?

Thanks for any suggestion.

Steven
 
J

Jerry Whittle

First off you have Score in your example but the SQL is doing the Max of the
Students.Number field. Which is it?

Next drop the DISTINCTROW as it's probably confusing things.

Also why is the StudentID field different for the same people? Mary is 2, 5,
and 8. Somewhere you need a way to uniquely identify the student and
FirstName+MiddleName just doesn't hack it as there could be duplicates. There
could be more than one John Doe.

Also them are some strange MiddleNames.
 
K

Ken Sheridan

Your table needs to be broken down into separate related tables, Students and
Scores. The Students table will contain one row per student with a unique
StudentID column (an autonumber can be used for this), FirstName, MiddleName,
LastName and other columns which represent attributes specific to the student
entity type. The Scores column will have a StudentID column as a foreign
key, but not an autonumber in this case, and a Score column. Do not use
names as the keys as two or more students could have the same name. So with
your sample data the table the students table would look like this

StudentID FirstName MiddleName
1 John Doe
2 Mary Jane
3 Peter Smith

The Scores table like this:

StudentID Score
1 10
2 20
3 18
1 30
2 22
3 30
1 19
2 25
3 33

To get the highest score per student and return their first and middle names
join the tables on StudentID and group by the StudentID, FirstName and
LastName columns like so:

SELECT StudentID, FirstName, LastName,
MAX(Score) As [High Score]
FROM Students INNER JOIN Scores
ON Scores.StudentID = Students.StudentID
GROUP BY StudentID, FirstName, LastName;

What you cannot do in a query, however, is show the individual scores on
some lines and the highest scores on others. To do that you should use a
report based on the following query:

SELECT StudentID, FirstName, LastName, Score
FROM Students INNER JOIN Scores
ON Scores.StudentID = Students.StidentID;

Group the report on StudentID and give it a group footer. In the group
footer put an unbound text box with a ControlSource property as follows to
show the highest score for each student:

=Max([Score])

Ken Sheridan
Stafford, England

stvlai said:
Hi

How are you guys doing? I have a question about using SELECT and MAX
function. Say I have a Students Table with Fields StudentID,
FirstName, MiddleName, Score

StudentID FirstName MiddleName Score
1 John Doe 10
2 Mary Jane 20
3 Peter Smith 18
4 John Doe 30 ......
John's high score
5 Mary Jane 22
6 Peter Smith 30
7 John Doe 19
8 Mary Jane 25 ......
Mary's high score
9 Peter Smith 33 ......
Peter's high score

I use a Max statement to extract out each Student Max Score e.g
SELECT DISTINCTROW Max(Students.Number) AS [High Score],
Students.FirstName
FROM Students GROUP BY Students.FirstName;
Correct Results : John 30 Mary 25 Peter 33

Since I also need their Middle Name, I added their MiddleName field
too. BUT it return all the Rows (9 Records), which is NOT correct. e.g

SELECT DISTINCTROW Max(Students.Number) AS [Max Of Number],
Students.FirstName, Students.MiddleName FROM Students GROUP BY
Students.FirstName, Students.MiddleName;

How can I select each Student Score with their FirstName and
MiddleName?

Thanks for any suggestion.

Steven
Click to expand...
 
S

stvlai

Hi

Thanks a lot for the tips / help. The data is not real. Just to help
to present my questions in a simple way. I will try the suggestions.

STeven

Your table needs to be broken down into separate related tables, Students and
Scores. The Students table will contain one row per student with a unique
StudentID column (an autonumber can be used for this), FirstName, MiddleName,
LastName and other columns which represent attributes specific to the student
entity type. The Scores column will have a StudentID column as a foreign
key, but not an autonumber in this case, and a Score column. Do not use
names as the keys as two or more students could have the same name. So with
your sample data the table the students table would look like this

StudentID FirstName MiddleName
1 John Doe
2 Mary Jane
3 Peter Smith

The Scores table like this:

StudentID Score
1 10
2 20
3 18
1 30
2 22
3 30
1 19
2 25
3 33

To get the highest score per student and return their first and middle names
join the tables on StudentID and group by the StudentID, FirstName and
LastName columns like so:

SELECT StudentID, FirstName, LastName,
MAX(Score) As [High Score]
FROM Students INNER JOIN Scores
ON Scores.StudentID = Students.StudentID
GROUP BY StudentID, FirstName, LastName;

What you cannot do in a query, however, is show the individual scores on
some lines and the highest scores on others. To do that you should use a
report based on the following query:

SELECT StudentID, FirstName, LastName, Score
FROM Students INNER JOIN Scores
ON Scores.StudentID = Students.StidentID;

Group the report on StudentID and give it a group footer. In the group
footer put an unbound text box with a ControlSource property as follows to
show the highest score for each student:

=Max([Score])

Ken Sheridan
Stafford, England





stvlai said:
Hi
Click to expand...
How are you guys doing? I have a question about using SELECT and MAX
function. Say I have a Students Table with Fields StudentID,
FirstName, MiddleName, Score
Click to expand...
StudentID FirstName MiddleName Score
1 John Doe 10
2 Mary Jane 20
3 Peter Smith 18
4 John Doe 30 ......
John's high score
5 Mary Jane 22
6 Peter Smith 30
7 John Doe 19
8 Mary Jane 25 ......
Mary's high score
9 Peter Smith 33 ......
Peter's high score
Click to expand...
I use a Max statement to extract out each Student Max Score e.g
SELECT DISTINCTROW Max(Students.Number) AS [High Score],
Students.FirstName
FROM Students GROUP BY Students.FirstName;
Correct Results : John 30 Mary 25 Peter 33
Click to expand...
Since I also need their Middle Name, I added their MiddleName field
too. BUT it return all the Rows (9 Records), which is NOT correct. e.g
Click to expand...
SELECT DISTINCTROW Max(Students.Number) AS [Max Of Number],
Students.FirstName, Students.MiddleName FROM Students GROUP BY
Students.FirstName, Students.MiddleName;
Click to expand...
How can I select each Student Score with their FirstName and
MiddleName?
Click to expand...
Thanks for any suggestion.
Click to expand...
Steven
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SELECT MAX(value) from table1 and add several associated values from table2 10
Excel Puzzle: Tracking Change in Hours 6
Grouping - Distinct 5
Select Top Query 2
ranked report? 1
Headache on Outer Join with Crosstab 1
Easy question: Sort ascending in a row? 3
Putting subtotals in $ descending order 1

Top