Correctly Populating Data Fields

[Geneva Anderson]

I am designing a database of contractors that I will search according to the
services they provide. I find most contractors have more than one specialty,
so my challenge is how do I enter all specialties of one contractor and be
able to search. For instance, I have contractors who do "Doors, Windows,
Sales and Installation." But when I search for a contractor who installs
windows, nothing comes up as a result of the query. I suspect the data in
the "Services Provided" field is not entered properly. I have other listings
such as "General Contractor" or "Painter" and can pull up those very easily,
but the problem seems to be with a contractor with multiple services.

What is the solution?

 

[Klatuu]

If you don't already have one, you need a Services table. It would contain
the Name for the service, a Code to identify the service, and the primary key
value of the contractor table. You would then want to create a combo box
with this table as its row source to do the lookup.

 

[Geneva Anderson]

Thank you. Still a little over my head but will take the key words and learn
about each one and try to pull this together. Suspected I needed a Services
table so need to export this data field into a separate table.

 

[John W. Vinson]

I am designing a database of contractors that I will search according to the
services they provide. I find most contractors have more than one specialty,
so my challenge is how do I enter all specialties of one contractor and be
able to search. For instance, I have contractors who do "Doors, Windows,
Sales and Installation." But when I search for a contractor who installs
windows, nothing comes up as a result of the query. I suspect the data in
the "Services Provided" field is not entered properly. I have other listings
such as "General Contractor" or "Painter" and can pull up those very easily,
but the problem seems to be with a contractor with multiple services.

What is the solution?

It sounds like you're storing all the specialties in one field... right?

No, wrong! Fields should be "atomic", and have only one value.

Here you have a Many (contractors) to Many (specialties) relationship. The
proper way to handle this in Access uses THREE tables, not one:

Contractors
ContractorID <Primary Key>
CompanyName
<other info about the contractor as an individual>

Specialties
Specialty <Text, Primary Key - e.g. "Windows", "Doors", "Plumbing">

ContractorSpecialties
ContractorID <link to Contractors>
Specialty <link to Specialties>
<any info about this contractor's performance in this specialty, e.g. a
Comments field, a checkbox for Licensed, etc.>

Rather than putting three specialties into one textfield, you would use a
Subform to add three RECORDS to the ContractorSpecialties table.

John W. Vinson [MVP]