Matching students with teachers

[Hurricane]

Dear all,

I'm quite new in Access and would like to implement a matching function.

1. I have a table with teachers (name, qualification and interests) and a table with students (name, target qualification and interests).

Can I create a query that only shows pairs of teachers and students which qualification /target qualification are identical?



2. Further, can I sort all pairs as following:

Show all pairs that match in both qualification and interests
Then, show all pairs that only match in qualification
then, show all other pairs which doesn't match in qualification

Thank you very much in advance!

[rpeare]

what is your data structure? Or do you not have one yet?

What do you do if you have multiple teachers and multiple students with the same qualification/target qualification?

[orange]

What if a teacher or student has multiple interests? What exactly are the underlying business facts.

A teacher may have 1 or Many qualifications
A teacher may have 1 or Many Interests
...

Facts and data structure are critical to getting a more focused response(s).

[TommyK]

Table1 - Teachers with name employee ID, etc. TEacher has one and only one record here.
Table2 - Interests With teacher identifier, either name or employee ID, along with an interest field. One interest one field, teacher could have many interests (records) here.
Table3 - Qualifications same as Table2, but with qualifications instead of interests.

Student tables as above.

Then queries to match the above.

As always, make sure you add a record entry date to all tables, as this seems to eventually become important. - [EntryDate] field with a default: Date().

[Hurricane]

Yes, both teachers and students can have 1 or several (up to 8) qualifications and 1 or more interests.

What if a teacher or student has multiple interests? What exactly are the underlying business facts.

A teacher may have 1 or Many qualifications
A teacher may have 1 or Many Interests
...

Facts and data structure are critical to getting a more focused response(s).

[Hurricane]

I usually find a number of matching teachers for each student, one by one.

For example, student1 has five matching teachers. I'd like to show all matching teachers in a list and sort them, first, by the number of matching qualifications, and second, by the number of matching interests.

In my example, teacher1 has 3 qualifications and 2 interests that match with student1 - this would be the first best match.

Teacher2 has 3 qualifications and 1 interest in common with student1 - the second best match.

Teacher3 has 1 matching qualification and 5 interests - the third best match etc.

I assign the best possible match to the student1 WHICH IS NOT TAKEN BY OTHER STUDENTS YET.

Thank you for yor support!

what is your data structure? Or do you not have one yet?

What do you do if you have multiple teachers and multiple students with the same qualification/target qualification?

[rpeare]

I don't think there is a way to do this without using code to cycle through recordsets, are you comfortable using VBA to solve this problem?

[JLCan]

Hi Hurricane!

The match can be done with very little VBA but good deal of fancy programming!!!
My solution will work with up to 64 qualifications/interests using lots of logics:

1 - Create a table for Qualifications with the following fields
Qualification - auto number;
QualTitle - text;
BitNo - byte ' You will see later its usage!!!

2 - Create a table for Teachers with the following fields:
Teacher - auto number;
TeachQualif - long; ' Here you store the qualifications as a bit map
StudentMatch - long; 'In case of match, store the student ID - Students.Student
...

3 - Create a table for Students with the following fields:
Student - auto number;
StudQualif - long; ' Here you store the qualifications as a bit map
TeachMatch - long; 'In case of match, store the teacher ID - Teachers.Teacher
...

4 - Define the following query:
SELECT Match([TeachQualif],[StudQualif]) AS Aff, Teachers.Teacher, Teachers.TeachQual, Students.Student, Students.StudQualif, Teachers.StudentMatch, Students.TeachMatch
FROM Teachers, Students
WHERE (((Teachers.StudentMatch) Is Null) AND ((Students.TeachMatch) Is Null))
ORDER BY Match([ProfQual],[StudQual]) DESC , Profs.ProfID;

N.B.- This is called in French 'Produit cartésien de 2 tables': every record of table A will be connected with every record of table B: this means handle with care!!!
There are many useless fields remove them after tests or create a simpler version of it.

5 - Define the following function:
Public Function Match(I As Long, J As Long) As Byte
Dim K As Long
Dim N As Byte, Bi As Byte, Bj As Byte
Match = 0 'Correction was Bid
K = I And J
If K <> 0 Then
For N = 0 To 63
Bi = I And 1
Bj = J And 1
If Bi = Bj And Bi <> 0 Then _
Match = Match + 1
I = Int(I / 2)
If I = 0 Then _
Exit For
J = Int(J / 2)
If J = 0 Then _
Exit For
Next N
End If
End Function

Does it make sense to you? Since I am retired from IT, I may hand up with a too technical solution; I am not sure there is a simpler one.
Any way I have plenty of idle time so if you want, I will send you a practical but partial solution.

Good work, JLCantara.

[orange]

Hurricane,
Can you please provide some sample data for what you are trying to do?

2 or 3 teachers
some interests and qualifications
some students with interests and qualifications

then some of your anticipated matches showing match and no match results.

We are all trying to help, but need to understand you, your abilities and the issue/opportunity you face.

Based on JL's approach you may want to review this on logical operators for additional operators that may be applicable to your approach.

Good luck with your project.

[JLCan]

I Hurricane!

I unfortunately took your problem lightly... Since I am curious and have tons of spare time, I made an application that solve your problem. Well we are not talking about complexity: I would rather call it fancy coding. Access likes to make simple things complicated; working on your matching problem, I had to avoid numerous traps. Anyway, give it a try! You will find the zip version as an attachment.

Good luck, JLCantara.