Matching People (Table 1) with Organizations (Table 2) Based Upon Interest (Table 3)

[kelann]

Hi, all!



I'm new here, and I'd like to thank you in advance for your help.

I've got what, for me, is a bear of a request for assistance. The main concept around all is that I'm trying to match young professionals with organizations based upon interests. The interests table (InterestsT) includes things such as "Art," "Entrepreneurship," etc. Both the Organizations table (OrganizationsT) and the young professionals table (YPT) will access InterestsT.

It's in 3 parts:

PART 1 - Live updating of InterestsT

On the YP form (YPF), where YP data will be entered, there will be a combo box that will access the InterestsT. InterestsT will (1) NOT limit to the list (so people can add interests "on the fly and as new organizations/YPs are added) and (2) be a multi-select field. I would like these added interests to append to the InterestsT. Ditto for teh OrganizationsT. Any thoughts on how to do this?

Part 2 - Matching YPs to Organizations and vice/versa

So, now that I've got YPs, each with one or more interest and Organizations all with one or more interests, I want to match them up on a new Form -- MatchingF. I would like people to be able to have the choice of selecting either an Organization or a YP. Then I would like this to happen:

1. The choices their selection made in InterestsT will be displayed. Also, the data from the memo field in the selection's record (OrgMemo/YPMemo) will be displayed.
2. They will press "Go" or something.
3. A list of YPs/Organizations (respectively) that have at least one InterestT in common with the selected YP/Organization will be displayed, along with each of their InterestsT selections. Also, the data from the memo field in each of the potential match's Memo field will be displayed.

Part 3 - Making the Match

1. Ideally, they could select one of these possible matches, which would then populate a Match field in each of the corresponding Org/YP records, but that might be asking too much, if I'm not asking too much already.

I surely appreciate any help you can give me.

I'm rather new to both Access and VP Script, but I seem to have a tiny breakthrough each day. I just wouldn't even know how to search for this one!!!

--ak

[June7]

Will the Interests field in YPT be a multi-value? Consider very carefully before committing to multi-value fields. The search you want to do on interests could be frustratingly complicated. Saving new interests 'on-the-fly' from a multi-select combobox might be even trickier, possibly not even possible. I NEVER use multi-value field.

http://office.microsoft.com/en-us/ac...001233722.aspx

[kelann]

Will the Interests field in YPT be a multi-value?

Hi, June!

Thank you for your reply. The original idea for the interests field in YPT and OrgT would be a combo box that looks up the values from the InterestsT. However, now that I'm back at work, I just tried to see if you can multi-select that way, and it appears you can't.

I'm open to the best way to accomplish this. The idea is for each of Orgs & YPs to select interests from the same choices, then to match them up.

I was wanting a list that could be added to by the user if the choice did not already exist.

I don't know all the options, but I thought, as an alternative, I could have a separate checkbox field for each interest. Each of OrgT and YPT could have these fields. There could be an "other" field, a checkbox, with an OtherDescribe Text field. To do the match, they could select a record and then press a button that runs a query on the selected YP or Org to see if ArtYP = ArtOrg OR EntrepreneurshipYP = EntrepreneurshipOrg, etc., to create matches.

If you have any suggestions, I would be very grateful for them!

--ak

[June7]

A multi-select combobox must be bound to a multi-value field.

A multi-select listbox does not but if you still want to allow multi-select the listbox could not be bound and would need code to save each selected item as individual records.

I was wanting a list that could be added to by the user if the choice did not already exist.

Check out the NotInList event of combobox http://www.blueclaw-db.com/access_no...ed_example.htm
Also, AllowValueListEdits and ListItemsEditForm properties http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I don't recommend a checkbox for each interest. This is not normalized structure and will probably give you trouble. Each YP can have many interests and each Interest can be associated with many YP. This is many-to-many relationship and requires a junction table. Review
http://forums.aspfree.com/microsoft-...es-208217.html

[kelann]

Thank you, June!

I ended up using a separate checkbox for each field (as you no doubt saw in my other post), just because I couldn't think of any other way of matching YPs to Organizations. I'll just do an "Other" checkbox with a "Describe" field as the final one. I tried googling Access Dating Compatibility Query (it seemed like a good analogy to me, and I thought maybe someone had asked about this or done it before), but I didn't get the results I had hoped for!

My progress is very slow -- this is all very new to me -- so I am open to any suggestions.

Thanks again!

--ak