Searching for values and making new tables

[Chevlion42]

pkstormy has this excellent posting in the Code Repository - https://www.accessforums.net/code-re...ords-7690.html - and it seems like it might be a partial answer to an issue I've been researching in Access 2003 for over 6 months.

I posted a reply asking if his method could be adopted to my situation but I didn't realize that the Code Repository was not the appropriate place for asking new questions. So I'm posting a new thread here in the Programming forum.

Here is the text of my reply to his original posting:

"Thank you for posting this. I think your method might address a concern I've been researching and trying to figure out for months.

I'm building an Access 2000 file format database for a membership organization which is beginning a mentoring program (I started building it in Access 2003 but my office upgraded to Office 2007 a few months ago). The 3 key tables are Mentees (people requesting mentors), Mentors (people wanting to be mentors), and Mentoring Categories (subjects for which mentoring is available). What I've built so far is suitable for keeping track of the two groups of people and maintaining the list of mentoring categories. However, I also want to be able to search the mentors table and pull up possible matches for new mentees. Ideally, that search would also generate a report of any matches made.

The fact that your example creates a separate table where the search results are stored leads me to believe that it could be adapted to my database. Do you agree?



If your method isn't appropriate for what I'm trying to accomplish, is there another method I could use?

I would appreciate any advice or direction you might be able to offer. In the 6 months that I've been working on this database, your method is the closest to the kind of searching that I have in mind that I've been able to find.

Thanks again for doing your posting.

P S: I have attached screenshots of each of the 3 tables in my question above - Mentees, Mentoring Categories, and Mentors - to my reply posting."

I have attached the screenshots of my 3 tables to this new posting. I hope that you can access pkstormy's programs through the link to his posting at the top of this message so you can see how his method works.

Any advice or direction this forum could offer would be greatly appreciated.

Thank you!

[June7]

This example shows each mentee and mentor having only one category. Mentors can't have multiple specialties? Mentees can't be mentored for more than one category?

Maybe I am missing something major here but if the above is true, don't you just need to do a join on the MentoringCategories fields to find possible matches? Use this query as basis for a Crosstab to show a matrix. Then someone has to make a decision on who to match up.

[Chevlion42]

Thank you for your reply. I haven't done that much with joins, but I will experiment with them. What kind of join would be best, an inner join?

I've built crosstab queries for other databases so I have some experience with that concept. Is there a way to create a table or report from a crosstab query so matches made can be recorded? Once that's done, can I also make the database send emails to the mentors chosen?

To answer your questions:

1) "Mentors can't have multiple specialties?" Yes, mentors can offer more than one specialty but I'm developing a different method of recording that information and want to keep it separate from this issue.

2) "Mentees can't be mentored for more than one category?" Initially, we thought it might be less confusing for mentees to request help with one category at a time. They will not be limited as to the number of times they could request mentors, so they could be mentored for as many categories as they want.

Here are my questions in one place in case they got lost in my paragraphs above:

For matching a mentee with a mentor:
- What kind of join would be best, an inner join?

For using crosstab queries to do the matching:
- Is there a way to create a table or report from a crosstab query so matches made can be recorded?

- Once that's done, can I also make the database send emails to the mentors chosen?

Thank you again for your reply and for the suggestion of using joins and crosstab queries. I will work with them and let you know how it goes.

[June7]

Here are the queries from my test effort.

SELECT tblMentees.ID, [tblMentees].[LName] & ", " & [tblMentees].[FName] AS MenteeName, tblMentors.ID, [tblMentors].[LName] & ", " & [tblMentors].[FName] AS MentorName, tblCategories.Category, tblCategories.ID
FROM (tblCategories RIGHT JOIN tblMentors ON tblCategories.ID = tblMentors.Category) INNER JOIN tblMentees ON tblMentors.Category = tblMentees.Category;

TRANSFORM First(Category) AS FirstOfCategory
SELECT tblMentees.ID, MenteeName
FROM Query1
GROUP BY tblMentees.ID, MenteeName
PIVOT MentorName;

Building a report for dynamic (field names can be different each time run) CROSSTAB means building it all new every time the query is run. Use the SELECT Join query to build report. These queries present possible matches. The CROSSTAB matrix makes it easy to see the possible matches without a report. You still have to record the matches in another table.

Yes, VBA code can automate sending emails.

[Chevlion42]

Thank you for redoing your last reply. The way you edited it by separating the queries' code made it easier for me to read it and to duplicate the queries. (I incorporated them into my database by building new queries and modifying their code to follow your examples.)

I was able to tweak the TRANSFORM query into three versions so it presents the SELECT query grouped by MentorName, MenteeName, or Category. I've pasted my code for those queries at the bottom of this posting.

However, this doesn't quite address my original concern and the reason I thought pkstormy's SearchStringExample2000 database might be useful - I was hoping to find a query or search which automatically recorded its results in a new table or report, the way his database does.

I understand your statement from your last reply - "You still have to record the matches in another table." - but I'd like to know if it's possible for the database to create that table automatically without making the users do it themselves.

Please don't misunderstand me. I really appreciate your advice on writing the SELECT and TRANSFORM queries. I had previously only used crosstab queries to count values and didn't realize they could also be modified to sort a query's results alphabetically. I also had not worked with queries' SQL views before; the only coding I've done in my databases until now has been in the coding for forms and their command buttons. Your examples were written clearly and I was able to adapt them with little trouble.

I've read and heard about make-table and append queries. Are they techniques which might be useful for what I'm trying to do?

Thank you again for your help and coaching.

Here are the adapted versions of your test queries:

Crosstab query – Grouped by MenteeName
TRANSFORM First(Category) AS FirstOfCategory
SELECT MenteeName
FROM qryMentorCategories
GROUP BY MenteeName
PIVOT MentorName;

Crosstab query – Grouped by MentorName
TRANSFORM First(Category) AS FirstOfCategory
SELECT MentorName
FROM qryMentorCategories
GROUP BY MentorName
PIVOT MenteeName;

Crosstab query – Grouped by Category
TRANSFORM First(MentorName) AS FirstOfMentorName
SELECT Category
FROM qryMentorCategories
GROUP BY Category
PIVOT MenteeName;

[June7]

Could write records for the possible matches. But is that helpful? If the query shows that a mentee could be matched with more than one mentor, do you want records for both? Or should there be a record only for the match that is actually assigned? That requires someone to make a choice before a record is committed. The possible matches could be presented on a form bound to the SELECT query. User clicks on choice and record is created by code.

[Chevlion42]

You're right about writing records for possible matches not being helpful. I have queries which do that but I want something that can record actual matches as they're made.

More importantly, your latest reply shows that you understand what I'm trying to do with my database.

Your question - "Or should there be a record only for the match that is actually assigned?" - is at the heart of what I want to do because I want there to be a way to keep track of mentor/mentee matches as they're made. Before my initial posting on this forum, I had already written a couple of queries which could give me snapshots of all the potential matches but I want a way to search for single matches on demand.

Your statement - "The possible matches could be presented on a form bound to the SELECT query. User clicks on choice and record is created by code." - indicates that what I want to do might be possible. Since the database's users would mostly not have that much experience with Access, I love the idea of doing the matches by clicking.

What kind of code would be needed to create the record generated by the SELECT query's form? And could it be tied to a command button?

Thank you for your help and for your patience. I can't tell you how much I appreciate it.

[June7]

I use only VBA, no macros. Code could be a button Click or Combobox or Listbox AfterUpdate.

Example of saving values from selected row of Listbox.

DoCmd.SetWarnings = False
DoCmd.RunSQL "INSERT INTO tablename (MenteeID, MentorID ) VALUES(" & Me.ListBox.Column(0) & ", " & Me.ListBox.Column(1)
DCmd.SetWarnings = False

[Chevlion42]

Thank you for your latest response. I haven't had a chance to check it out yet because work has been busy the last couple of weeks.

Also, I realized this morning that the focus of my question should be slightly different. While the work we've been doing with crosstable queries has been helpful, those queries return all possible matches in the database. In other words, they return every mentor and each category for which that mentor offers coaching.

What I actually need is a query which can search for all the mentors offering coaching for a specific category. That is, if Fred is looking for help with Career Development, I need a button or query which will allow me to select the category from the list, then search the Mentors table and return all the mentors who have listed Career Development as one of their categories.

If this could be accomplished, I wouldn't be so concerned with saving the matches in a new table. The user performing the search and matching the mentee with a mentor could manually record the match in a separate table, without the recording being automatic.

So my new question is this: Is it possible to combine a drop-down list (of the mentoring categories) with a query so I can run the query on a specific value?

Thank you again for all your help. If you have any insight on my revised question, I would appreciate it.

[June7]

Yes, that is simply filtering with a parameter input. The input would be by reference to the combobox value. The query could be the RecordSource for a form or report. Check out this tutorial http://www.datapigtechnologies.com/f...tomfilter.html