Re: [MS_AccessPros] Option box?

 

Graham,  It's been a while since you gave me this advice - I apologize for the delay but I still need help!

 

My original tables are:

    ReportPatrol Record which includes PatrolRecordID (Auto numbered - PK), and other stuff that I assume won't be necessary for the junction table.

    ObedienceSkills which includes ObedienceID (Auto numbered - PK) and ObedienceDesc.

    SkillRatings includes SkillRatingID (PK) and SkillRatingDesc.

 

The junction table:  ObedienceRatingsJunct has JunctID (Auto number and PK), ObedienceID, PatrolRecordID, SkillRatingID, Comments.  (You said to have a numeric field for the skill rating but will a separate table work?)

 

I want my subform to list the ObedienceDesc along the left side and the SkillRatingID along the top with all of the radio buttons lining up below and right.  It will need to be linked to the PatrolRecordID.

 

First of all, does ObedienceRatingsJunct look like it has what it needs to make this work?

 

Second, in your original response, you said I could pre-populate the subform with all known skills which is exactly what I want to do so I'm trying to figure out the append query in VBA you proposed (Greek to me!).  Can you tell me how I would go about making an append query?   

 

Thank you for any help you can give me!

 

Jessica

 

On Friday, November 21, 2014 3:11 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hi Jessica

Since the skills are recorded for each evaluation, the foreign key in your junction table should be the evaluation (ReportRecordID) not the dog.  That way, each skill is evaluated in each report, and there can still be multiple skill measures per dog.

You may not have noticed it in these terms, but ReportPatrolRecord is already a junction table, joining Dogs and Handlers.

Good luck!
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 22 November 2014 03:05
To: MS_Access_Professionals@yahoogroups.com
Subject: 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 (5)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___