How do I find the smallest of 4 field values in the same record o.

T

TehraniCHS

I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it in ACCESS.
 
T

Tom Wickerath

Your easiest bet, at this point, is likely to export your table to Excel and use the MIN function
that you are familiar with using. Your database is not normalized if you have separate fields in
a table to store similar data, like grades. This makes querying such a structure very difficult.

You really need a minimum of 2 tables, with a one-to-many relationship between them:

tblStudents
pkStudentID (Primary Key. I suggest using Autonumber)
FirstName
LastName
+ any other attributes about the student that you wish to store

tblGrades
pkGradeID (Primary Key. Again, I recommend using Autonumber)
fkStudentID (Foreign key. Use Long Integer if you use an Autonumber in tblStudents)
AssignmentNo
Grade

You will likely want a third table, tblAssignments, in which case you would use fkAssignmentID
(Long Integer) in place of "AssignmentNo" in tblGrades.

tblAssignments
pkAssignmentID (Primary Key. Autonumber recommended)
AssignmentDesc
MaxScore
DueDate

You would then create relationships between your tables (Tools > Relationships), between the
primary and foreign key fields. A query to help you determine the lowest grade is very simple
with the structure outlined above. I can help you with that, if you are interested in
normalizing your current database.

Tom
_____________________________________


I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it in ACCESS.
 
T

TehraniCHS

Thank you; I knew I could export & do it in EXCEL, but the problem is this is
a UIL test question and the table structure is given to the students in the
problem and they are supposed to show their solution for calculating the MIN
in Access and Not EXCEL. I would appreciate it if you could tell me how to
do the query with the 2 linked tables using the MIN function. Thank you.

Mansoureh
 
T

Tom Wickerath

Mansoureh,

Sorry, but if I had known that this was a test question, given to you by your professor, I would
*not* have volunteered an answer. As a former part-time instructor of an Access course at
Bellevue Community College (Bellevue, WA. USA), I would not appreciate a student trolling the
newsgroups for a solution in the manner in which you did. You should have clearly indicated in
your first post that this was a classroom assignment. I have no need to earn points for you. On
the other hand, you should try exercising the grey matter a bit more before posting such
questions. You will be served better in the long run if you learn to think for yourself!

That said, there are a few possible routes for you to consider. However, you didn't give enough
information. Are you needing the lowest overall grade for the population of students, or the
lowest grade for each student? Are you allowed to use VBA code?

Hint: An easy non-code method would likely involve using a Union query. Look up the use of union
queries and see if you can figure it out on your own.

Good Luck, and please be a bit more honest in any future postings that relate to assigned work.

Tom

___________________________________


Thank you; I knew I could export & do it in EXCEL, but the problem is this is
a UIL test question and the table structure is given to the students in the
problem and they are supposed to show their solution for calculating the MIN
in Access and Not EXCEL. I would appreciate it if you could tell me how to
do the query with the 2 linked tables using the MIN function. Thank you.

Mansoureh
___________________________________



Your easiest bet, at this point, is likely to export your table to Excel and use the MIN function
that you are familiar with using. Your database is not normalized if you have separate fields in
a table to store similar data, like grades. This makes querying such a structure very difficult.

You really need a minimum of 2 tables, with a one-to-many relationship between them:

tblStudents
pkStudentID (Primary Key. I suggest using Autonumber)
FirstName
LastName
+ any other attributes about the student that you wish to store

tblGrades
pkGradeID (Primary Key. Again, I recommend using Autonumber)
fkStudentID (Foreign key. Use Long Integer if you use an Autonumber in tblStudents)
AssignmentNo
Grade

You will likely want a third table, tblAssignments, in which case you would use fkAssignmentID
(Long Integer) in place of "AssignmentNo" in tblGrades.

tblAssignments
pkAssignmentID (Primary Key. Autonumber recommended)
AssignmentDesc
MaxScore
DueDate

You would then create relationships between your tables (Tools > Relationships), between the
primary and foreign key fields. A query to help you determine the lowest grade is very simple
with the structure outlined above. I can help you with that, if you are interested in
normalizing your current database.

Tom
_____________________________________


I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each record
based on the 4 fields (grade1, grade2, grade3, grade4). I know how to use
MIN function in EXCEL and do this, but I want to know how to do it in ACCESS.
 
T

TehraniCHS

I am not the student; I am the teacher. Each year, I help a few of my
students prepare for the UIL competition. The problem I was refering to here
was in one of the packets to help them prepare and it may be similar to the
ones they would get during the competition. I would never ask others to help
me with my test.

Thank you,

TehraniCHS
 
T

TehraniCHS

I was so upset by your remarks that I forgot to repond to your last question.
The problem asks them to figure the Min grade for each student. I will look
up Union Query and see if I can figure it out. Thanks for your help.

TehraniCHS
 
T

Tom Wickerath

Mansoureh,

I'm sorry if I upset you. Your first post had me thinking that you were indeed a teacher, but
your second post looked a bit more suspicious. Please understand that we do get students posting
their questions in the newsgroup, looking for a quick answer, instead of showing a willingness to
tackle a problem themselves.

For the current table structure, where you indicated in your first message:

"I have a table with records of students with their grades in various
assignments. I want to do a query to find the lowest grade in each
record based on the 4 fields (grade1, grade2, grade3, grade4)."

Let me start by outlining the schema for a table that I think is close to your table. I named it
tblStudentGrades:

tblStudentGrades
Field FieldName Datatype

1 pkStudentID Autonumber (Primary Key)
2 FirstName Text (15)
3 LastName Text (20)
4 Grade1 Number / Integer
5 Grade2 Number / Integer
6 Grade3 Number / Integer
7 Grade4 Number / Integer

In this example, I am assuming that each grade can only be an integer value. If you need to
accommodate a number with a decimal, then change the datatype to Number / Single.

Add some data to your table. Then create the following union query. To create a Union query,
start with a brand new query, but do not select any tables. In query design view, click on View >
SQL View. You should see the word SELECT highlighted. Replace this word with the following SQL
statement:

SQL statement

SELECT FirstName, LastName, Grade1 AS GRADE FROM tblStudentGrades
UNION
SELECT FirstName, LastName, Grade2 AS GRADE FROM tblStudentGrades
UNION
SELECT FirstName, LastName, Grade3 AS GRADE FROM tblStudentGrades
UNION
SELECT FirstName, LastName, Grade4 AS GRADE FROM tblStudentGrades;

Save this query as: quniAllGrades
When you run it, you should see each student's name paired with each grade, as a separate record.

Create another new query. In the Show Table dialog, select the Queries tab. Select your new
union query, quniAllGrades. Click the Add button and then the Close button. Click on View > SQL
View again. Paste the following SQL statement into the SQL view:

SELECT pkStudentID, LastName, FirstName,
Min(quniAllGrades.GRADE) AS MinGrade
FROM quniAllGrades
GROUP BY pkStudentID, LastName, FirstName
ORDER BY LastName, FirstName;


Tom
___________________________________


I was so upset by your remarks that I forgot to repond to your last question.
The problem asks them to figure the Min grade for each student. I will look
up Union Query and see if I can figure it out. Thanks for your help.

TehraniCHS
 
T

TehraniCHS

Thanks Tom. I really appreciate the help. I will try it tomorrow. I am
sure it will work. Thanks again,

Mansoureh
 
Top