Can this be done with a relationship connection?

[Charlotte]

Hi- I am an utter newbie at Access...just started tonight. I managed to set
up a database, table and a form for my contacts. The form lists all the
services that each contact provides (painting, drywall, carpentry, etc.).
I'm wondering if there is a way to input their skill set so that i'll easily
be able to search for it later. So, for example, 123Contractor is a painter,
but also does carpentry and drywall work. ABC Contractor does drywall,
stairwell and exterior shingles. So, down the road i will want to search for
all the contractors that do drywall work, how do i input that info so it can
be easily searched?

I'm sorry to be so basic, but it seemed intuitive enough when i started &
now i'm pretty deep in. Good news is that this will basically hold their
contact info, a record of when i've called them & which clients they've
worked on for me. (I'm inputting my client table tomorrow).

I think once I understand the concept of relationships & linking all this
stuff up together, I'll be able to do these basic searches....or at least
that's the goal. Thanks in advance for any help you could provide.

 

[Charlotte]

Actually, I am wondering if this kind of 'sort' (excuse my Excel lingo) could
be better accomplished on QuickBooks? I am getting that program shortly and
maybe it would be better to do this all there?

 

[Klatuu]

Don't know about Quickbooks' capabilities, but to address your Access
question, the way to do this required two additional tables. One table would
be a list of skills. That table needs two fields:
tblSkillSet
SkillID - AutoNumber Primary Key
SkillDescription - Text, Plain language to describe the skill

And Since the Contact to Skill is a many to many relationship (one Contact
may have 0 to many skills and one skill can be held by many Contacts), you
need a Contact/Skill Table to resolve the many to many relationship and
thereby identify the skills each Contact has. This is commonly known as a
Junction table. It needs only two fields. One to hold the primarky key
value of the Contact and One to hold the primary key of the skill the Contact
has. This means that for each contract, there can be 0 to the number of
records in the Skill table records in this table:

tblContactSkill
ContactID - Foreign Key to Contact Table
SkillID - Long Integer - Foreign Key to tblSkill

The combination of the two fields should be the Primary Key of this table.

To associate the skills with a contact, you need a subform on you Contacts
form to enter and display the contact's skills. The Record Source for the
subform should be a query based on tblContactSkill and tblSkill.

To display the skill records for the contact, you would use the ContactID of
the Contact table in the Link Master Field(s) property of the subform control
and the ContactID of the query in the Link Child Fiekd(s) property.