tournament registration

[KB]

I am trying to build an Access database for a martial arts tournament. There
are a number of variables in determining the competition division (fly,
bantam, light, etc.) including age and sex. Basically, I want to be able to
select the correct division based on the age and sex entered on an entry form.
It would be acceptable if the correct table was selected based on the age
group and sex entered. i.e. I have tables called: Female6-7, Male8-9 etc.
When an entry is made that selects the age range and sex, the correct table
is selected to determine the division. I am a very novice user and this has
stumped me completely

 

[John W. Vinson]

I am trying to build an Access database for a martial arts tournament. There
are a number of variables in determining the competition division (fly,
bantam, light, etc.) including age and sex. Basically, I want to be able to
select the correct division based on the age and sex entered on an entry form.
It would be acceptable if the correct table was selected based on the age
group and sex entered. i.e. I have tables called: Female6-7, Male8-9 etc.
When an entry is made that selects the age range and sex, the correct table
is selected to determine the division. I am a very novice user and this has
stumped me completely

I would suggest a somewhat different design. You certainly should NOT have a
different table for each agegroup/sex combination! Data should be stored in
tables, not in table names.

A better design would have a Divisions table with fields like:

DivisionID <Number or Autonumber, Primary Key>
DivisionName <Text, e.g. "Male 8 - 9">
Sex <Text 1, validation rule IN "M", "F">
AgeLow Number
AgeHigh Number

with rows like

1; "Girl's Fly"; "F"; 6; 7
2; "Boy's Fly"; "M"; 6; 7

etc etc.

You can easily use a Query with criteria based on combo boxes or text fields
on a form to select the appropriate division record. I would recommend not
selecting the age but rather the birthdate - you can calculate the age as of
the scheduled time of the competition using an expression like

DateDiff("yyyy", [DOB], [CompetitionDate]) - IIF(Format([DOB], "mmdd") <
Format([CompetitionDate], "mmdd"), 1, 0)


John W. Vinson [MVP]

 

[Larry Daugherty]

From what you've given, you need only a single table that will hold
the records for all players: tblPlayer

That table would have an Autonumber Primary Key, the player's
LastName, FirstName, Middle, DateOfBirth, Gender, Notes

You don't *ever* need tables like "Female 6 - 7" and "Make 8 - 9". If
and when you need information like that you'd present it in a Form
based on a Query.

You never want your users (even if you are the only user) to be
messing directly in the tables. All interaction with the data should
be with Forms and Reports.

HTH