Wondering if something is even possible?

[acepayne]

Hi there,

I wasn't sure whether to post in reports or queries, as I do want to end up with a report. I reckon it will all start with a query, however, so here goes..

I have a situation where I will be keeping track of people that are registered for a convention. Now, each person will be associated with a "region", and each region has what I'll call a pre-determined "voting strength". So, depending on how many people show up for a given region, these people will have a calculated number of votes.

This is probably best illustrated with examples:

Let's say Region A was a voting strength of 22 votes. If only one Region A person registers for the convention, he/she will cast all 22 votes. If two people show up, each person would get 11 votes.

Now, here is where the complication sets in. If 3 people were to register, they would get 7.33333 votes each, wich, of course, doesn't seem feasible.

What I would like to do in a situation like this (this is what I would like the report to show eventually), is have two people with 7 votes, and the third with 8. It doesn't even matter which of the three people gets the extra vote.

So, the query would need to get a count of persons registered, grouped by region, and then, IF POSSIBLE, show the votes each person would be entitled to, and in the case of un-even numbers - assign an extra vote to someone.

I hope this makes some sort of sense, and if it is possible, any suggestions would be awesome. Even if it looks like it CAN'T be done, please let me know.

Thanks so much!

Cheers,
Ace

 

[Jon K]

Alternatively, you can use the \ and Mod operators to find the average votes and remainders in each region and assign the votes to the people in a loop.

I have attached a sample database. You can open the form and click on the command button to assigned the votes from the Votes table to the People table.
.

 

[acepayne]

Thanks!

Wow, thanks guys, for the effort you put into finding a solution for me!

I was thinking that some code might be the way to go, and looking at Jon K's suggestions, this is something that will definitely work for me.

Currently I do not store the calculated vote count with the people/attendee records. I will, however, require a report that lists all attendees and these numbers. Should I add such a field to the people/attendee table? Or would I be better off using a temp table for the purposes of the report, and fill it with the data garnered from the VBA code? I guess what I'm concerned about is ensuring the vote count calculation is done anytime someone is added to (or removed from) the people/attendee table, or when the "voting strength" data is changed for a region.

What do you guys think? Temp table, or new field?

Again, thanks so much for your help!!

Cheers,
Ace