Making multiple fields searchable in microsoft access tables/queries

[sianpembs]

Hi there,

I have a microsoft access database comprising several tables, one of which holds details on volunteers, and another which holds details on clients who need volunteers. One client needs one volunteer. Each table contains three fields "Hobby1", "Hobby2", and "Hobby3", each of one lists one hobby of theirs. What I want to be able to do is to match up volunteers and clients based on the compatability of their hobbies. Ideally what I'd like to do is click on a record in the client table and be able to find the most compatible volunteer based on the compatability of their hobbies, which would bring up a rank order of compatible volunteers, or display compatability in terms of percentage. I don't know if this is even possible, and have no idea of how to go about it. Any help would be very very very gratefully appreciated!!

Thanks in advance,

Sian.

 

[Golom]

This would be easier if you had a normalized database with a "Hobbies" table

- VCode
- Hobby

Then the code is simply

Select V.Volunteer, Count(*) As Matches
From Volunteers V INNER JOIN Hobbies H ON H.VCode = V.VCode
Where H.Hobby (... matches the Requirement ...)
Group By V.Volunteer
Order By 2 DESC

You need to decide what filter conditions are appropriate for (... matches the Requirement ...)

Without normalization it's a whole lot uglier.

Select V.Volunteer, 
    SUM ( 
          IIF(Hobby1 Matches Requirement, 1, 0 ) +
          IIF(Hobby2 Matches Requirement, 1, 0 ) +
          IIF(Hobby3 Matches Requirement, 1, 0 ) ) As Matches

From Volunteers V 

Where V.Hobby1 (... matches the Requirement ...) OR
      V.Hobby2 (... matches the Requirement ...) OR 
      V.Hobby3 (... matches the Requirement ...) 

Order By 2 DESC

 

[BEHUMAH]

This can definately be done. I don't know how familiar you are with Access. If you have one table with just volunteers, another table with just hobbies (assign them a primary key field of Hobbies), and another field with the clients, you can just create a query with the criteria just being the common interests.

 

[willir]

...Actually, let's top for a moment.

Golom (long since I last saw you active -- welcome back) ... is absolutely correct. You should consider normalizing your database.

Now the reason to pause is...
Do you want to categorize / match people by their hobbies? You answered yes.

So you actually have a many-to-many relationship here...
One person can have many hobbies.
A hobby can be enjoyed by many people.

Consequently, to do this properly, you should consider adding two tables...

Now, I am guessing that you probably have one table for clients and one table for volunteers -- this would seem to be a natural way of doing things. This approach is challenging -- how to create a M:M but linked with two tables. Probably lost you on this one...
[tt]
Client M: <------> :M Hobbies
Volunteers M: <--> :M Hobbies
[/tt]

Personally, I would actually have only one table, tblContact and then classify the type of contact -- volunteer or client (or both??)

One approach with this single cotnact table would be...

tblContact
ContactID - primary key
ContactType - text (Volunteer, client)
ContactLN - last name
ContactFN - first name
... plus other info for the cotnact

tblHobby
HobbyCode - primary key
HobbyDescription

tblContactHobby
ContactID - foreign key to tblContact
HobbyCode - foreign key to tblHobby
HobbyLevel - text, level of experience (novice ... expert)

Primary key = ContactID + HobbyCode

Using a Hobby table, and a many-to-many relationship between the contact and the hobby, you can be sure that name used for a hobby will be consistant. For example...
Ski
Skiing
Sking
Skier

It would be difficult to match / group by hobby if different spellings are allowed.

Why the HobbyLevel?
Just optional -- Would allow you to capture the level of experience. For example, a volunteer novice skier may not be a good match for a client who is an expert.

...Now, as mentioned, I suspect you may have used two tables to capture the contact info -- clients and volunteers. If so, a tweak would be...

tblvolunteer
VolunteerID - primary key
VolunteerLN - last name
VolunteerFN - first name

tblClient
ClientID - primary key
ClientLN
ClientFN

tblHobby
HobbyCode - primary key
HobbyDescription

tblContactHobby
ContactType - text, Volunteer or Client
ContactID - foreign key to tblVolunteer or tblClient
HobbyCode - foreign key to tblHobby
HobbyLevel - text, level of experience (novice ... expert)

Primary key = ContactType + ContactID + HobbyCode

For further reading on normalization...
Fundamentals of Relational Database Design (by Paul Litwin)
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

A more advanced topic with a superb, but brief overview...
Harnessing the Power of Updatable Queries

Richard