Assigning records to a group.

[gemadan96]

We're developing a database to manage tournament registrants as well as event results.



The key tables we have ar:

• Contacts
• Registrations
• Registration Details
• Tournaments
• AgeGroups
• BreakingAgeGroups
• ExperienceGroups
• Events
• Payments

There are queries for using information in the tables to determine age, competition divisions etc.

At the moment the RegistrationExtended query, which uses the ContactsExtended and TournamentsExtended queries and the Registrations table to determine the number of events for each registrant as well as what division and age group they are assigned. This is done with some IIF expressions.

For the 2012 event we had 4 division classes based on experience and 6 age groups. For 2013 there might be more division classes and age groups. So now I have the tables AgeGroups, BreakingAgeGroups, and ExperienceGroups. The two age group tables have a query that calculates the age group name based on the values in the MinAge and MaxAge fields. For example 8 & Under, 9-11, 12-14 etc. The ExperienceGroups also have a minimum and a maximum field but only one field will be completed for each experience level. If the value is a maximum then that would be for those with less than the maximum experience and if the value was a minimum then that would be for those with minimum of that many years of experience. For example

• Grasshopper for less than 1 year
• Warrior for those with 1 year to less than 2.5 years
• Samurai for those with 2.5 years and less than 4 years
• Shogun for those with 4 or more years.

We want to maintain the history so the age groups and experience groups are now tied to the tournament.

I'm think that I'll need some VBA code that will compare the registrant's tournament age to the ranges assigned to that tournament and will assign them to the correct age groups. Then there will be code to compare the registrant's experience and assign them to the correct division class. The key here is that each year might have a different number of age groups and experience groups as well as different experience group names. For instance in 2013 we may change the 8& Under group to 7-8 and add a 6 & Under group and we may add another level called Emperor for those with 20 years or more experience.

So can this be done and how?

[June7]

Need to save the appropriate group designation with the registration.

If you want code to control this and the conditions will change, then will have to revise code when those conditions change. Otherwise, have user select the group when the individual is registered.

[gemadan96]

Do you think there is anywaty to do this with a dlookup?

[June7]

I expect it could, but the issue is the same. If you add groups, the criteria changes and the code would have to be changed regardless if code is in macro, VBA, or query.

Do you want to retain historical data? Save the group designation with registration so that changes don't affect existing records.

[gemadan96]

In each of the "Group" Tables is a field TID to link the group to a specific tournament. So for each tournament there will be a record set for AgeGroup, BreakingAgeGroup, and Experience Group. The values used from those tables along with the EventClass from Registration details will be used to determine the division name such as 9-11 Warrior Traditional Forms or 15-17 Female Point Sparring. We will then use additional queries and reports to get a count of how many are registered for each specific division.

I'm wondering if it's possible in coding to use some sort of IF Then Next loop based on the number of records in each table for each tournament to determine which groups a competitor belongs to.

We're trying to limit the amount of data entry for the user to limit errors. We also want to maintain the historical information.

[June7]

So if you later add the Emperor level that won't change the existing categories and the respective age ranges associated with them?

I seldom encounter something that isn't possible. So I will yes, what you want could be done but I just don't undertand your data structure well enough to offer specifics. What do you mean by 'each' of the Group tables? This sounds like non-normalized data structure. If these 'group' tables have identical fields then really should be one table with another field for the 'group' attribute.

[gemadan96]

I'm thinking that I'll break this down into two parts, accomplish one then move onto the next. Anyway I've revised the ExperienceGroups table to have 4 fields ExpID (primary key), TID (identifies the tournament), GroupName, and LessThan (number of years experience must be less than this value). I've added this table to the RegistrationsExtended query and joined it based on the TID. I've added 4 groups for the 2012 event and 5 for the 2013 event. So now when I view the RegistrationsExtended query I see multiples of each registration and as it turns out there are four for each 2012 registraion (1 for each ExperienceGroup) and five for each 2013 registration. So now if I can get it to compare the [Experience] value to the [LessThan] value and return the corresponding group that would be great. The experience levels are entered in ascending order so I would like it to return the first group that the experience is less than. For 2012 there are four levels Grasshopper - less than 1 year, Warrior - less than 2.5 years, Samurai - less than 4 years, and Shogun - less than 100 years. So if .5 is entered for experience it will return Grasshopper, if they enter 1.5 it returns Warrior, etc.

I was also looking at the possibility of doiung this with a subquery.

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[gemadan96]

Here's the database.

[June7]

Records in Contacts have no Birthdate and no Sex. Should these be required fields?

Problem with RegistrationsExtended query is the ExperienceGroups table. It has 4 records with TID 3 and 5 records with TID 1. So when this table is joined to the TournamentExtended table, every TID 3 record in TournamentExtended is joined to each of the TID 3 records in ExperienceGroups. Same case for TID 1. Remove ExperienceGroups from the query and 259 records show. So join is not going to work with this structure and maybe have to use DLookup. This seems to work:

DLookup("GroupName","ExperienceGroups","TID=" & [Registrations].[TID] & " AND LessThan>=" & [Experience])

[gemadan96]

That did it for the experience groups.

Yes some of the fields in the current data are empty. This is all development at the moment. We will be requiring those fields starting with 2013.

Next objective is to do something similar with the age groups. There are age group designations for breaking events and for all the other events. Just like Experience levels those groups may change. For instance for 2012 our general age groups were 8 & Under, 9-11, 12-14, 15-17, 18-34, and 35 & Over. For 2013 we may start with 5 & Under, 6-8, and so on.

Working with just the AgeGroups I have an MinAge, MaxAge and added a LessThan field. Based on the registrants calculated TournamentAge in RegistrationsExtended they should be placed in the appropriate age group. In my AgeGroupsExtended query there is a calculated field to determine the name of the age group.

Using a similar expression to what was used for the experience group would it make sense to have three criteria in the expression: TID (as above) AND TournamentAge >= MinAge AND TournamentAge <= MaxAge or continue using the LessThan>Tournament age and just use the Min and Max to name the group?

[June7]

Try those ideas and let me know what happens.

[gemadan96]

I went with the LessThan path, less typing. I modified it with an IIF function so that if the experince or age is null than Unknown is returned (instead of #error).

Here's the one for Experience Groups:
Experience Group: IIf(IsNull([Experience]),"Unknown",DLookUp("GroupName","ExperienceGroups" ,"TID=" & [Registrations].[TID] & " AND LessThan>" & [Experience]))

Thanks. This resolves a lot.