RE: [MS_AccessPros] Option box?

 

Hi Jessica

Yes, there is a better way  J  If you do this with multiple fields in your main table, you will create a nightmare when you want to add a new skill.

You probably already have a table of people/users/whatever with a primary key (call it PersonID).  Create a table of skills with one record for each possible skill – SkillID (PK), SkillName, SkillDescription, etc …

Now create a junction table (PersonSkills) linking the two.  This table needs a foreign key for PersonID (PersonFK) and a foreign key for SkillID (SkillFK).  Both should be related one-to-many to their respective tables.  PersonFK/SkillFK should be a unique composite index to ensure you can have only one instance of a given skill for each person.  The junction table also needs a numeric field, SkillLevel, and perhaps a text field for comments.

Now your form can be a simple subform, with the SkillName displayed or selectable from a combo box, and the SkillLevel bound to an option group with button values from 0 (n/a) to 6. The master/child links for your subform should be on PersonID/PersonFK.

If you want to pre-populate the subform with every known skill, you can do this easily by executing an append query in VBA code:

sSQL = "INSERT INTO PersonSkills (PersonFK, SkillFK) SELECT " & Me.PersonID & " AS PersonFK, SkillID as SkillFK FROM Skills"
CurrentDb.Execute sSQL

Good luck!

Graham Mandeno [Access MVP 1996-2014]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 21 November 2014 10:30
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Option box?

 

 

Hello group!

 

I am trying to build a form that would allow the user to rate a skill on a scale of  n/a or 1-6.  There are multiple skills and each skill will need a response each time the report is made.  It would look something like this but with radio buttons:

                            n/a     1     2     3     4     5    6

Skill 1                    x

Skill 2                                    x

Skill 3                                                         x

etc.

 

Is this best done by giving each skill a field in the form table and using an option box for each or is there a better way?

 

Thanks!

Jessica

__._,_.___

---

Posted by: "Graham Mandeno" <graham@mandeno.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___