Unanswered: Performing matches between Access 2003 tables?
I am designing an Access 2003 database for an organization which wants to match mentors and mentees. There are two basic tables - tblMentors and tblMentees - where each group's contact information is stored. Each table refers to tblMentoringCategories, the list of subjects for which mentoring is available, and stores the choices in a field called CategoryID (the primary key from tblMentoringCategories).
I have two design issues which I haven't been able to resolve.
The major issue is this: How can I make the database generate matches between mentors and mentees? For example, Abby needs someone to coach her on filing expense reports. Freda has 15 years of experience and is willing to help anyone having trouble with expense reports. Ideally, I would enter Abby as a new mentee and search the Mentors table for anyone (including Freda) who could coach her. How can I perform that search? Also, I want to be able to save any matches for future reference.
The minor issue is this: I want to set up the mentors table so each potential mentor could select multiple categories for which they would provide coaching. How can I do this and store each mentor's multiple choices (if any)?
Any advice or direction you could provide will be greatly appreciated. Thank you!
you've posted the mentors table twice, presumably the mentees table is similar
ON mentors.categoryid = mentees.categoryid
WHERE mentees.personid = 937 /* abby */
to record multiple categories for each of them, you'll need two additional relationship tables, consisting of two columns, mentorid/menteeid and categoryid, foreign keys for their respective tables... and of course the query then becomes a bit more complex
as for saving the search results, i wouldn't bother, because the data can change and you'd want to rerun the query anyway