Help with organizing numbers

[buddyrich2]

I need some advice concerning organizing numbers in a field.

I have a list of baseball tournaments and one field lists all the age groups available in that particular tournament like this "6U 7U 8U 9U..." Sometimes a number is skipped if the tournament doesn't have a category for that particular age. I want to change the field to say (something like ) "6U - 9U, 11U" when one is skipped or if it is just consecutive all the way to the end say "6U - 16U".

Any ideas?

 

[TamarGranor]

Putting all the age groups into a single field is not really a good idea. The right way to do this is to have a table this lists tournaments, a separate table that lists age groups, and a third table that links the two. They'd look something like this:

Tournament

iID - primary key
cName - tournament name
dStart - start date
dEnd - end date
...


AgeGroup

iID - primary key
cDesc - description
nMaxAge - maximum age for this age group
...

TournamentAgeGroup

iID - primary key
iTournamentID - foreign key to Tournament
iAgeGroupID - foreign key to Age Group
...

For reporting, you could still do something like you're describing by collecting the information for a single tournament and processing it, but for data storage, a separate record for each is a much better idea.

Tamar

 

[buddyrich2]

Right, I realize that, but the problem is they are already in the format I described (I scrape them off the net). I am trying to split the string up and I don't know how.

 

[TamarGranor]

Ah, that's a different problem. So you grab something like "6U 7U 8U 9U" and you need to create the data. If it's really that regular, this is a piece of cake. (My experience is that stuff you scrape isn't all that regular.)

Assume you've stored that string in cAgeGroups. To parse it, just use ALINES():

nAgeGroups = ALINES(aAgeGroups, m.cAgeGroups, " ")

That gives you one array element for each group. Now you can create your records.

Tamar