Hi everyone. I'm building a feature into a database that will allow committees made up of employees to cast votes on job candidates based on criteria that are specific to their committee. I have the following tables:

Employees

Committees

Candidates

Committee_Employees (join of Employees and Committees)

Review_Assignments (join of Candidates and Committees)

Committee_Voting_Criteria (fk_committee_id)

Employee_Vote (fk_employee_id, fk_candidate_id_, fk_criterion_id, vote_ a 1-5 number)

This seems pretty straightforward, but

I'm stuck.. I want to build a layout that will allow an Employee to login and cast votes based on the criteria specific to the committee that they're on.

I'm not sure how to put everything together.. can anyone help?

thanks

committee_votes.fp7.zip

My guess is that I should write a "cast vote" script that sets the employee_ID and applicant_ID as variables and then goes to the vote table and creates necessary records and goes to a layout that displays those records .. waiting for the user to enter numbers into each of the vote criteria.

Personality o 1 o 2 o 3 o 4 o 5

Presentation o 1 o 2 o 3 o 4 o 5

And, the script would just go to related records if the vote was already cast.. so that the user could change their vote.

any advice anyone can offer would be awesome..

cheers.

Yes, I think you've got it. Because the answer is always chosen from the same values (1-5), then you can remain relational. All you need is the relevant IDs and the answer field. The records in the vote table would need to be pre-created, on the spot as needed; a Loop would be sufficient, Import would also work. And, as you say, Go To Related Record if the vote records already exist.

It would be very easy to count the votes.

I'm still a little confused about how to ensure that I create all the related records that are necessary for the votes.

What does the loop need to do to know that it has created one vote record for every related vote criteria?

You need a table that has the Committee and its "votes" (or whatever you're calling these). It would have the committee ID, and either the names or IDs of the vote names for that committee.

Personality

Presentation

Since there's likely overlap between committees for this, you'd likely want another table for the above words, so that you can use just IDs for them in the Committee-Votes join table.

The two above are what I call "reference" tables, in that you fill in the info manually once, to be used mostly as value lists for IDs for other "data entry" tables.

The Loop would either go through this table, for the current committee, creating the necessary "data entry" records, ready for voting on. Or you'd set all the relavent IDs into a Variable (using List() if you've got 8.5), then loop through them.

Thanks Fenton... I am able to get the votes into a votes table. I'm not sure how to tabulate averages for the candidate though. For each candidate, I would like to tabulate an average per voting criteria and also then an average of those averages, to come up with a final score per candidate. I'd like to display such a final score average in list view from the point of view of the candidates in list view.

how_do_I_tally_vote_averages.fp7.zip