Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Question 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!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    [bump] - can anyone help this guy?

  3. #3
    Join Date
    Jul 2009
    Posts
    4

    Screenshots of tables to perform matches between.

    I'm attaching screenshots of the 3 tables I'm trying to perform matches between to this reply.
    Attached Thumbnails Attached Thumbnails tblMentees Screenshot.gif   tblMentors Screenshot.gif   tblMentoringCategories Screenshot.gif  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've posted the mentors table twice, presumably the mentees table is similar

    pseudo-sql --
    Code:
    SELECT mentors.mentorid
         , mentors.lastname
         , mentors.firstname
      FROM mentees
    INNER
      JOIN mentors
        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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •