Best Match Query

[Robert]

I need to create a query or queries, that return the number of matches on
corresponding fields in two tables. Table 1 is Applicants, Table 2 is
Mentors. Fields A1, A2, A3..., M1, M2, M3... are populated with a/b/c/d type
values. I think it should be fairly simple to find the number of interests
in common between Applicant1 and Mentor1, Applicant1 and Mentor2, etc. but I
can't get started. Can someone point me in the right direction?

The ultimate output is to identify the applicant/mentor pairs with the most
in common, but that might be obvious if we can accomplish the above.

TIA,
Robert

 

[Olduke]

I need to create a query or queries, that return the number of matches on
corresponding fields in two tables. Table 1 is Applicants, Table 2 is
Mentors. Fields A1, A2, A3..., M1, M2, M3... are populated with a/b/c/d type
values. I think it should be fairly simple to find the number of interests
in common between Applicant1 and Mentor1, Applicant1 and Mentor2, etc. but I
can't get started. Can someone point me in the right direction?

The ultimate output is to identify the applicant/mentor pairs with the most
in common, but that might be obvious if we can accomplish the above.

TIA,
Robert

Create a query which uses both tables. Have fields A1 A2 A3 M1 M2 and M3 in
the query
In the Criteria for A1 enter:
= M1
On the second line in the criteria for A1 enter:
= M2
On the third line of the criteria for A1 enter
= M3

Do the same for A2 and A3

 

[John Spencer]

Your problem is that your data structure is wrong. You really need more
tables.
Applicants
-- ApplicantId
-- LastName
-- FirstName
ApplicantInterests
--AppicantID
--Interest

Similiarly, for Mentors
Mentors
MentorInterests

Then you can get a count of the matches. You present structure is going to
require a VBA routine to count matches or the use of "normalizing" uniion
queries to get the data into shape so you can run a query to get the count

The normalizing UNION queries would look something like the following
qUApplicants:
SELECT Applicants.ApID, A1 as Interest
FROM Applicants
UNION
SELECT Applicants.ApID, A2 as Interest
FROM Applicants
UNION
SELECT Applicants.ApID, A3 as Interest
FROM Applicants

qUMentors
SELECT Mentors.MentID, M1 as Interest
FROM Mentors
UNION
SELECT Mentors.MentID, M2 as Interest
FROM Mentors
UNION
SELECT Mentors.MentID, M3 as Interest
FROM Mentors

Final query would then look like
SELECT A.ApID, M.MentID, Count(*) as InterestsInCommon
FROM qUApplicants as A INNER JOIN qUMentors as M
ON A.Interest = M.Interest
GROUP BY A.ApID, M.MentID
ORDER BY A.ApID, Count(*) Desc

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..