queries and forms

[Sarah Knight]

I am modifying a database where the goal is to link people/providers with
their skill set. I have three tables (Provider,Skills,ProviderSkills) set up
and am hoping that someone can help me move forward with the development.
The tables have the following fields:

Provider (this table was created long agoand fields cannot be modified)
Provider ID - Text
First Name
Last Name
Contact Info, etc

Skills
Skill ID - Text
Skill - Text

Provider Skill
ID
Provider ID
Skill ID

I would like to design a form where I can enter a providers skills based on
the skill list but am not sure how to set the query up (I have tried a
million different ways and have been unsuccessful) to draw the data
correctly. Any suggestions on this step in the process?

Ultimately, I would like to query for a set of skills and generate a list of
providers that match.

 

[Jason Lepack]

Form to enter Providers Skills:
1) Create a form based on Provider
2) Create a subform based on Provider Skill, linking the two forms on
Provider ID
3) Create a combo box on the subform that looks up data in Skills to
be placed into Skill ID in Provider Skill.
4) Done

To look up all providers for a specific skill then you could set up a
form like the one above, just the opposite way:
1) Create a form based on Skills
2) Create a subform based on Provider Skill, linking the two forms on
Skill ID
3) Create a combo box on the subform that looks up data in Provider to
be placed in Provider ID in Provider Skill.
4) Done

Cheers,
Jason Lepack

 

[Tim Ferguson]

I would like to design a form where I can enter a providers skills
based on the skill list but am not sure how to set the query up (I
have tried a million different ways and have been unsuccessful) to
draw the data correctly. Any suggestions on this step in the process?

The right answer depends on your users... Do they start with a bunch of
skills and distribute them to providers, or start with a provider's
record and add skills to them? Or some other work pattern?

Assuming it's the second variety, you presumably have already started
with a form based on a query based on the Providers table. My favourite
method then is to add a list box based on a RowSource like

SELECT SkillID, Skill
FROM Skills RIGHT JOIN ProviderSkills
ON Skills.SkillID = ProviderSkills.SkillID
WHERE ProviderSkills.ProviderID = Forms!ProviderForm!ProviderID
ORDER BY Skill

so that the list of skills added to this provider is visible in plain
text. It's probably better to hide the first column of the list box, but
not strictly neccessary if the SkillID entries are human legible.

If you want to remove a skill from a provider, you add a button that
removes the ProviderSkills record vis:

DELETE FROM ProviderSkills
WHERE ProviderID = Forms!ProviderForm!ProviderID
AND SkillID = Forms!ProviderForm!SkillsList.Value

and don't forget to add a SkillList.Requery afterwards or else the user
won't see the entry disappearing.


If you want to add a skill that the Provider doesn't have, you place
another command button, and get it to open another form. This form just
has an OK button, a Cancel button and a list box. The listbox is based on
a RowSource which looks up all the skills that the current provider does
not have.

SELECT SkillID, Skill
FROM Skills
WHERE SkillID NOT IN (
SELECT SkillID
FROM ProviderSkills
WHERE ProviderID = Forms!ProviderForm!ProviderID
)
ORDER BY Skill;

Using the OK button causes a record to be INSERTed into the
ProviderSkills record; using the cancel button doesn't do anything to the
data.

You can use a drag-and-drop but it's messy in Access and I haven't
actually used it myself.



Hope that helps


Tim F