Creating a scoring form

[TrekBiker]

In a scoring system for events a candidate can choose one of a list of events, each having its own set of criteria which the candidate read and score individually. The exercise over a period of time is to build up a set of scoring averages for each criterion in each event. An individual candidate's score set could then be compared with the running averages for his chosen event.

My simple approach was for a candidate to choose an event on a main form, then a subform would show the specific criteria details for that event on separate pages, using the EventID as link field. The candidate would start at the first criterion, evaluate it, add a score on this page and move to the next criterion. I could then extract all scores for this candidate.

But the form wouldn't be reusable because another candidate choosing the same event would see the previous candidate's criteria scores unless they were automatically deleted. Clearly messy.

My difficulty is that I'm using the EventID to link to the criteria pages but want scores for each criterion to be linked to the CandidateID on the main form.

I'm clearly approaching it wrongly so any thoughts would be appreciated.

 

[dhookom]

It would really help if you provided you current table structure.

Do you expect the scores to be saved for each candidate? If so you should append records to a table with the candidateID and criterionID. Then every candidate should only see his/her criterion scores.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks as ever for your response. And yes, I want to record the set of Criterion scores for each candidate's choice of Award.

These are the simplified table relationships.

And this is the form the candidate would use for scoring, but not putting the score on the subform

 

[dhookom]

As I mentioned, you need to have a candidate scoring table with at least these fields:

[ul]
[li]CandidateID[/li]
[li]AwardName[/li]
[li]Criterion[/li]
[li]EntryText[/li]
[li]Score[/li]
[/ul]

I don't see a primary key on the Criteria table.
I'm not sure why you have AwardName in the Candidates table. This suggests a candidate can only have a single award. I would remove AwardName and use the Award Entered table as a junction table between Candidates and Awards. Again, I would add a primary key to each table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

I really appreciate your help with this. The junction table looks to be a good approach but not one I ever knowingly use. I'm not thinking straight on this anymore, so here are more details of the structure.

The background is that a number of companies have previously taken part in an Award event. There are many such events on different occasions and each Award type has a set of judging criteria titles like Summary, Objectives, Innovation etc. An Award candidate completes a form in which he fills in details of a particular initiative, so entering text details for each of the Award's judging criteria. Judges then assess the entire entry, and score it criterion by criterion.

I want to set up a judge assessment system. For this I am picking a set of best entries for each of the Award events. An assessment candidate will choose one of these Awards and will see the texts originally entered for each criterion. He will then judge and score each of the criteria. I want to build up statistical data on how different judges have assessed the same Award entries. This is how I arrived at my original frmCandidates, and the problem of making it reusable without associating a score with a criterion.

I feel sure your candidate scoring table is going to resolve this but haven't yet understood how to implement it. Any thoughts would be much appreciated.