Re: [MS_AccessPros] Option box?

 

Thanks Graham, I didn't think my way seemed like a good idea.

 

I apologize - I'm a rookie Access user.  What I have are obedience skills for dog training records.  I already had a table of ObedienceSkills with the ObedienceID and ObedienceDesc.  I also have (at this time) two dogs in my PSD table (PSDID and PSDName), two dog handlers my Handlers table (HandlerID and HandlerLastName), and a ReportPatrolRecord table which is the basis for my main for on which I would put this subform. 

 

Each dog's skills/performance will be evaluated frequently and recorded in ReportPatrolRecord table - should the junction table include ObedienceID and PSDID or ObedienceID and PatrolRecordID?  I'm thinking that since I want one instance per ReportPatrolRecord (rather than per dog) that I want to use ReportRecordID rather than (as in your example) PersonID.  Am I on the right track?

 

Jessica

On Thursday, November 20, 2014 5:04 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

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: Jessica Hoback <hobackjessica@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___