Compare&sort answers to the same Yes/No Questions by selected (a) student to (b) all families

[Vit_Dobrin]

Hello!

I am designing a database for a summer exchange school. There are two main tables – Students and Host Families. I am struggling to create a command that would connect any chosen student with possible suitable host families.

To determine, who is suitable, I created a table of 15 Yes/No questions, which are answered both by students and families. Now I would like to add a command button to the student form to create a query which would (1) search through all of the families and their answers and (2) compare these with the ones the chosen student gave (e.g. the student whose form is open). In the end it should (3) list all the families according to number of matching answers and also (4) show where they don’t match.



Basically, I am completely lost here.

Any help would be much appreciated! Thank you in advance,

Vit

[rpeare]

I don't know that I would do this with a query as you would have to do a cartesian join (basically a query with no link between the tables ensuring you get every possible combination of family and student) I do not know what kind of volume you're talking about but if it's a small number this might work just fine.

However it might just be easier to create a third table that is 'possible matches' that would be a junction table between your families and students then use code to cycle through the records this way you can pretty easily set a minimum threshold of matching questions (i.e. you won't consider matching a student and family if less than 8 questions were matching)

[Vit_Dobrin]

It is pretty small scale... About 20 students and 15 families... Might grow over time?! I believe though that cartesian join will work - so thank you for that! However all questions are important - less than 8 won't work, for example, even if a student and family match on all questions but one, this may not be acceptable, especially if student answered "yes" for "allergic to pets" and family answered "yes" to "we have pets"......

Nevertheless, I still can't figure out how to single out any chosen student to compare his answers to [answers of all of the families]. Any help/hints?

[rpeare]

what are you rules? do you want to rank each family based on how well they match the student? or do you only want the top possibilities? You've gotta have rules!

Do your tables have a primary key (autonumber field?)

So here's what you'd do, assuming you had fifteen fields (assuming this is a non normalized structure)

Match1: iif(student!q1 = family!q1, 1, 0)
and so on for each question then you could have a 'total matches' column as well:

TotalMatches: iif(student!q1 = family!q1, 1, 0) + iif(student!q2 = family!q2, 1, 0) + iif(student!q3 = family!q3, 1, 0) ... etc

where STUDENT is the name of the student table
FAMILY is the name of the family table
and qx is the name of the question field

[Vit_Dobrin]

1. I want to rank the families based on the number of matches (but I would still need to see which are the questions/answers that do not match)
2. My tables have primary key.
3. I have some sort of a normalized structure - I just updated my knowledge on what this exactly means and it turns out that I have it.

STUDENTS --- basic student info
--- etc...
----- HOST QUESTIONS ----------------- Host families
basic family info--
etc.----

I hope this makes at least some sense...

I will have to test your solution and will tell you how it turns out... Many thanks!