Query to lookup values in a table

[mscant]

I have 3 tables:

BaseData
DataPt1
GradesToPts

The BaseData table contains details about students such as name, gender, etc.

The DataPt1 table contains sets of grades for each subject a student studies (e.g. A, B, C).

The GradesToPts table contains a set of numerical equivalents to those grades (e.g. A has numerical value 7, B has numerical value 6, etc). These numbers will be needed for some maths later.

I want to design a query that returns students' names together with the numerical values of their grades. So, for example, if a student has achieved grades A, B and C in their subjects, the query returns their names together with the numbers 7, 6 and 5.

How do I achieve this in the design view of a query?

Thanks for any help offered,

Matt

 

[pbaldy]

In design view, add both tables to the grid above and if not already there, click/drag to create a join between the linking fields. Then you can add fields from both tables to the field list below.

 

[mscant]

Thanks Paul - that works great if each student only has one grade. If they have three grades, and I link them all to the Grade field in the GradesToPts table, no records are returned.

Any ideas?

 

[Minty]

I think your table design is proabably wrong, can you post up a picture of the query in design view or list the fields in each table?

 

[mscant]

Hi Minty,

Please see attached. Any help gratefully received.

Matt

 

[namliam]

Yeap minty is right, design issue.

What do you do if a student has 5 grades/subjects or 10 or 20?
The grades should be in rows not in columns...

If you are stuck with what you have you need to add your gradestopts table 3 times and join each table to a separete grade

 

[deanamiles86]

If you give each student a unique ref number you could use this


create a module

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
 
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null
 
SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value
 
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
 
' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)
 
Set cnn = Nothing
Set rs = Nothing
 
' return concatenated string.
Conc = vFld
End Function

then using your query posted earlier, create another query that groups by the Ref Num and group by User then this as an expression

Pts: Conc("Pts","Ref Num",[Pts],"Query1")

hope this makes sense

 

[mscant]

Dear all,

thanks for the replies thus far. Each student has a unique number called a UPN, which I already use as the primary key in the BaseData and DataPt1 tables.

Each student studies a number of subjects. For each subject, they may have up to three teachers assigning them a grade. I want to average these grades.

If I was using Excel, I would perform a vlookup on each grade for a particular subject, and then average the numbers returned, before converting this average back into a grade.

For example, if a student gets an A, a B and a C for Art from her three Art teachers, I would vlookup these grades to return 7, 6 and 5, then average the numbers (to give 6), before converting the average (6) back into a grade (B).

I thought I would be able to store all of the grades from all teachers and all subjects in one table, store the conversion from grade to number in a second table, and the basic information such as name etc in a third table. I want to create a query that returns the name of the students, the grades reported by each teacher, the lookup values of the grades, the average of those lookup values, and the grade corresponding to the average lookup values.

Hope that makes sense,

Matt

 

[Minty]

You need to store the results in a liner fashion something like

ResultID (PK)
StudentID (FK)
TutorID (FK)
CourseID (FK)
Grade

This would allow you to have any number of grades and tutors for any number if courses.

For this to work you would need a TutorTable and CourseTable as well.

 

[mscant]

OK - thanks Minty. I'll have another look at this and maybe get back to you if still no joy.

 

[deanamiles86]

To do that

Create a query based on your earlier query and group by the student num and subject, then average the pts, then use an if statement to determine the grade