Matching based on criteria from multiple tables

[Jonpro03]

I work for a small Adult Literacy program. Next week, I would like to create an application (hopefuly with Access) that can match Tutors to Students based on similar criteria.

There will be two similar tables, one for students and one for tutors. They will have identical fields, such as Name, Phone, Day(s), Time(s), Location(s) and Availability.

I would like for the software to be able to pair Student (records) with Tutor (records) that have similar criteria, namely days, times and locations.

Ideally, once a student and tutor are paired together, their records will not show up in future results. However, I would like to retain the record in the event that the pair should ever break and the tutor becomes available again.



Does this sound like something that Access is capable of doing? Can I get some direction on how to get started?


Thanks,
Jon

[June7]

Access is designed to serve this type of need.

First, if table structures are identical it might not be necessary to have two tables. One table (Participants) with another field for Category (student, tutor) might suffice. Either way, will need another table that records the pairings.

StudentTutorPairs
PairID (Autonumber primary key)
StudentID (foreign key)
TutorID (foreign key)
DatePaired

Then need data somewhere that can be used to filter participants in active pairings. Maybe in the Pairs table an Active (Yes/No) field.

[Jonpro03]

That's great news. I'm glad to hear that tutors and students can exist on the same table as it makes it MUCH easier for me to import data off of an existing database. I will get started on building the tables on Tuesday.

Here are the plans for the tables:

Participants table:
ParticipantID - Autonumber, Primary Key
LastName
FirstName
IsTutor - Yes/No
IsMatched - Yes/No
--then the matching critera--
Location
Days
Times
--There could be more criteria later. Also, the values for each field will be a defined lookup.--


StudentTutorPairs table:
PairID (Autonumber primary key)
StudentID (foreign key)
TutorID (foreign key)
How do I go about populating the StudentTutorPairs table? Will it be a query or some sort of macro? Can I link the Primary Key from the Participants to both the StudentID and TutorID?

Then maybe a form based on the StudentTutorPairs table that will display information pulled from the Participants table using the stored StudentID and TutorID for each PairID.

[rpeare]

How are you finding matches? Do they have to be perfect? How are you storing days and times of availability? Is your location a town name or zip code? Is it a street name?

What goes into those fields really determines how you would do data entry for them and go about matching people (or suggesting matches)

I'd also suggest that in your StudentTutorPairs table you keep a matchdate (as june suggested) and a terminationdate. When a pair is terminated (if they don't get along or the student becomes literate) the matching tutor can then be returned to the list of 'available' tutors. This way you don't really have to have an 'ismatched' field. The idea is to store as little data as possible and still be able to retrieve all the information you need via queries or calculations. You may also want to add a reason for the termination in case, for instance, you get a tutor who is breaking up with multiple students over time it may be an indication that the tutor is not suited to your system.

[Jonpro03]

How are you finding matches? Do they have to be perfect? How are you storing days and times of availability? Is your location a town name or zip code? Is it a street name?

I would like to have these matched in order of priority. For example, If there are multiple students available at a certain location, then the next priority will narrow it down further and so on. Priorities (as of now) are:

1st will be Location. Locations will be names of local libraries.
2nd will be Days. Mon-Sun and more than one can be selected (suppose all criteria can have more than one selection).
3rd will be Times. Simply Morning, Afternoons, Evenings.
4th --There will be other criteria, all text based--

The first two priorities have to be perfect matches. I would like to still display results if 3rd and 4th priorities are not met.

I'd also suggest that in your StudentTutorPairs table you keep a matchdate (as june suggested) and a terminationdate.

As it's been suggested twice, I'll most certainly add it in.

This is great information and I appreciate it. Please keep it coming!

[rpeare]

Your response leads to more questions. How many local libraries are you going to have in your database?

Can a person have say Monday Mornings, Tuesday Afternoons and Wednesday Nights, Thursday All Day? (you didn't mention an all day possibility but I would think that on a weekend or something it would be possible)

I have to assume you can so you not only want to store the day of the week but what time of the day of the week they are available.

Can a person, either student or tutor be paired with more than one person at a time? for instance can a student receive tutoring from person A on monday and person B on tuesday or is it strictly 1 to 1.

When you are matching a pair of people which person takes priority? I would think it would be the student, so in reality you are looking at a student's availability and trying to find them a tutor that fits their time slots so if the TUTOR has more available times than the student it would be considered a match (but not perfect in that they don't both have exactly the same availability)

[Jonpro03]

How many local libraries are you going to have in your database?

There are currently 24 locations.

Can a person have say Monday Mornings, Tuesday Afternoons and Wednesday Nights, Thursday All Day?

Simple answer, No. There is occasionally a rare exception. For example, if a tutor selects Fri, Sat, Evening, Morning, they have to be available on Friday morning and evening, and Saturday morning and evening. We discourage having a strict schedule.

you didn't mention an all day possibility

If they are available all day, I would like to be able to select Mornings, Evenings, and Afternoons. I think this would make matching easier as well.
On a side note, there has to be a minimum of two days selected. I would like to know how to have an error box pop up if only one day is selected.

Can a person, either student or tutor be paired with more than one person at a time?

No, this is a 1:1 tutoring program. However, multiple match results can be displayed, but a tutor can only be paired with one student.

When you are matching a pair of people which person takes priority?

As our program consistently has more students than tutors, I would say that the tutor takes priority in that I want every tutor matched to a student, with students leftover.

so if the TUTOR has more available times than the student it would be considered a match

This is true, but the opposite is true as well.
Also, there needs to be a minimum of two matching availabilities as tutoring takes place twice a week. So if there is only one Location/Day/Time match between a tutor and student, it is not a match.

[rpeare]

how many locations and time slots can the student and tutor have in common to be considered a match?

let's say you have a retiree that's volunteering and they are available 7 days a week, you are not going to try and find a student with that exact same availability, which leads to the question how many times a week is the minimum you expect your pairs to meet, what's the maximum.

I would expect in this case you would try to find the student with the highest availability and assign it to this tutor and let it be up to them how often then want to meet?

On the subject of maximum meetings per week. Does it have to be 2 different days per week, 1 day per week but two different time slots (monday morning and afternoon for example), 3 location/time slots in common? I think your matching problem is more complex than you think but it doesn't mean it can't be done

[Jonpro03]

how many locations and time slots can the student and tutor have in common to be considered a match?

They need to have at least one location in common and one time slot in common to be considered a match. Required minimum two days in common. Tutors and students meet twice a week.

I would expect in this case you would try to find the student with the highest availability and assign it to this tutor and let it be up to them how often then want to meet?

If I had a tutor with 7 days a week availability, I would find the student who has been waiting the longest with matching criteria. But this is not something I would expect the database to do.

On the subject of maximum meetings per week. Does it have to be 2 different days per week, 1 day per week but two different time slots (monday morning and afternoon for example), 3 location/time slots in common?

It will be two different days/wk, they are not allowed to meet twice in the same day.

I think your matching problem is more complex than you think but it doesn't mean it can't be done

Haha, I fully expect it to be complicated. That's why I brought the problem here.

[Jonpro03]

Here's an example.

Tutor: Bob
Location: North Library
Days: Tue Wed Thur
Times: Evenings

Student: Sara
Location: North Library <--- Match
Days: Thur Fri Sat Sun <--- Not a match, req'd matching days>=2
Times: Afternoons Evenings <--- Match

Student: John
Location: South Library, South East Library <--- Not a match, req'd matching locations >=1
Days: Mon Tue Wed Sat Sun <--- Match
Times: Mornings, Evenings <--- Match

Student: Georgia
Location: North Library, Central Library <--- Match
Days: Mon Tue Thur <--- Match
Times: Afternoons <--- Not a match, req'd matching times >= 1, but display result last

Student: Sam
Location: North Library <--- Match
Days: Wed Thur Fri <--- Match
Times: Evenings <--- Match

The output of this will show Sam first, followed by Georgia second.

It may be easier to cycle through the list of Tutors and view all available matching students. As we are reviewing the matches if we pair a student with a tutor, the student's record will be removed from future tutor matches.

[rpeare]

Here's what I would be inclined to try:

tbl_People
PeopleID PeopleType PeopleFN PeopleLN (other fields....)
1        T          Mickey   Mouse
2        T          Donald   Duck
3        S          Foghorn  Leghorn
4        S          Bugs     Bunny
5        S          Daffy    Duck

tbl_Locations
LocationID LocationName (other fields...)
1          North Library
2          South Library
3          West Library
4          East Library

tbl_Availability
AvailabilityID (autonumber)
LocationID (number)
PeopleID (number)
AvMon (yes/no)
AvMonMorn (yes/no)
AvMonAft (yes/no)
AvMonNight (yes/no)
AvTue (yes/no)
AvTueMorn (yes/no)
AvTueAft (yes/no)
AvTueNight (yes/no)
AvWed  (yes/no)
AvWedMorn (yes/no)
AvWedAft (yes/no)
AvWedNight (yes/no)
AvThu  (yes/no)
AvThuMorn (yes/no)
AvThuAft (yes/no)
AvThuNight (yes/no)
AvFri  (yes/no)
AvFriMorn (yes/no)
AvFriAft (yes/no)
AvFriNight (yes/no)
AvSat  (yes/no)
AvSatMorn (yes/no)
AvSatAft (yes/no)
AvSatNight (yes/no)
AvSun  (yes/no)
AvSunMorn (yes/no)
AvSunAft (yes/no)
AvSunNight (yes/no)

This is probably not the way I would do it if I were designing it but if you're unsure how to continue I think it will work out better for you.

You would have to build a query that would count the matches between pairs (i.e. make sure they have at least two av<day> checkboxes in common, and make sure the time slots for each day have a time slot in common) then sort the results by date. Depending on how you built the query you may have the same person suggested as a match multiple times (for instance if both tutor and student are available at three different libraries each and they have two in common you would get two records if their times and days matched).

[Jonpro03]

I've started building the tables. I can't upload the database to the forum because it's near impossible to get a DB under 500kb

So here's a link:

http://dl.dropbox.com/u/35173015/matching1.accdb

I messed around with a query for a while but I have no idea how to accomplish what I want. I'm open to any suggestions

[rpeare]

Here is your database back.

I've added a number of queries (this is a bit messy which is why I don't like non-normalized table structure) but it should work. Note I didn't do a full battery of tests so some of it may need to be recoded as you go along.

First you are missing a primary key in your locations table, that is an absolute must for this to work. Put in an autonumber field and call it LocID (as I did).

There are two union queries in this database that you can not view the design of, union queries will not allow design view changes. Instead I have left you with two basic queries named:

Qry_Basic_StudentFinder
Qry_Basic_TutorFinder

Because your table structure is not normalized you have to have five iterations of each of these queries in your union query, one for each possible location the person will be available. Note that the LOCATION is not the location field name, I've given it an alias so that on the union query there's no confusion.

My structure also assumes that anything in your matches table will be in order chronologically based on the MATCHID field, in other words you won't be entering a match for the same person for a later date before an earlier date.

After that it should be relatively clear what the rest of the queries are designed to do and the final query is limited to matches that have 2 or more matching days/times and is sorted by the enter date of the student in descending order (you indicated people who had been in the system longer are given priority). Note there is no mechanism for dealing with people who were given a match the match terminated. The easiest way to handle this in your current structure is to give them a new enter date that matches the date of their termination, that would give your database a way to handle people who had not actually had a tutor for the longest.

Anyway, here it is, the final query is Qry_Potentital_Matches.

[Jonpro03]

Fantastic! Thank you so much. I'll reply back if there's anything I'm unsure of.