Limit number of Duplicates

[DeputySmith770]

I have a registry for a competition...each team can only register four
groups for a particular event, and three for another, and five for a
third event. All events are seperate tables, related by Team Name. I
have set the team name field to allow for duplicates, but am curious
if I can limit the number of duplicates to the allowed number of
entries.

Can I set some sort of rule to allow for duplicates, not to exceed 4
(or any given number)? Should this rule or validation be set at the
table level, or in the form?

Thanks.

 

[pietlinden]

I have a registry for a competition...each team can only register four
groups for a particular event, and three for another, and five for a
third event.  All events are seperate tables, related by Team Name.  I
have set the team name field to allow for duplicates, but am curious
if I can limit the number of duplicates to the allowed number of
entries.

Can I set some sort of rule to allow for duplicates, not to exceed 4
(or any given number)?  Should this rule or validation be set at the
table level, or in the form?

Thanks.

bad design. put them all in the same table. otherwise, summarizing
is going to be a PITA. Trust me... I spent 6 weeks wrangling crappy
data. You can use DCount to get a count of records with the same
value in any combination of fields and then cancel the insert if that
person is over the limit.

 

[DeputySmith770]

I have solved the Duplicates problem by simply making sure the form
gathering team sign-ups is limited to four, and the person doing data
entry is aware of the four (or other entry limit) rule for the
different competition events.

I have done a few databases, but none this complex...and am not sure I
can follow your suggestion of single tables. Let me ellaborate a
little. I have a table that collects the school and coaches
information (SchoolName being the primary key), and tables for Stunt
Groups, Co-Ed Groups, Partner Groups, and Individual Competetors.
These are all linked back to the SchoolName. Each school can enter 4
Stunt or Co-Ed groups, 3 Partner Groups, and 5 Individuals.

Again, the common link to all of these tables is SchoolName. I have
then built a form to enter the data for the school and coach, with
subforms tabbed to enter the Stunt, Co-Ed, Partner, and Individual
groups.

Once all of this data is entered, I need to then capture the scores
from 5 judges for each group or individual into more tables, which I
will likely add as a connected subform to each group (see above for
list). I am at the point now where I am trying to create and link
these judging score forms to the competitor and/or event tables.
Judging is formulated like this: Five scores, with the highest and
lowest cancelled out, and the remaining 3 averaged, then any penalties
are deducted to reach a total score for the round (still working on
this formula!!!). Once scoring is done, I will be writing queries to
report the winners of the rounds, and randomly sort the top seeds for
the finals. In the finals they will again be scored the same way, in
an effort to crown a chamion for each group.

Complicated, I know. Any suggestions you can provide I would greatly
appreciate. I am continuing to work toward a final product, and will
take all input given.

Thanks in advance.

 

[Jamie Collins]

I have a registry for a competition...each team can only register four
groups for a particular event, and three for another, and five for a
third event. All events are seperate tables, related by Team Name. I
have set the team name field to allow for duplicates, but am curious
if I can limit the number of duplicates to the allowed number of
entries.

Can I set some sort of rule to allow for duplicates, not to exceed 4
(or any given number)? Should this rule orvalidationbe set at the
table level, or in the form?

Both: we call it "belt and braces" Doing the test in the front end
will prevent a round trip to the database and you will be able to give
more timely feedback to the user about limits. Putting a constraint on
the table(s) will prevent bad data getting into your database whatever
the source e.g. something you missed in the front end code, someone
connecting via Excel, etc.

For some ideas for a table constraint to limit rows according to a to
variable amount (i.e. data driven), see:

http://groups.google.com/group/microsoft.public.access/msg/1d4430dc8b50ba8f

Jamie.

--