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: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar