setup age groups in a table

I am putting together an activities table that will correspond relationally from another table.   I am trying to figure out how to label predetermined age groups and all I can think of is to put them in the following columns.  Since there can be more than one age group, for each activity, I figure setup 5 different columns for the age groups.  Do you see any concerns doing it this way?

SELECT TOP 1000 [ID]
      ,[Activity Name]
      ,[Activity Desc]
      ,[Activity Outcome]
      ,[Location]
      ,[To]
      ,[From]
      ,[0 to 7]
      ,[8 to 12]
      ,[13 to 17]
      ,[18 to 20]
      ,[21 and over]
      ,[PIID]
  FROM [CAPRegistration].[dbo].[tblOrgActivities]

Open in new window

al4629740Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
In that case, choices are...
*  The ranges as you have them in the form.  Probably the easiest choice.
*  Have the 'Option Group' a single value, and in your form handle writing  multiple values into an single value.  Handy, but also a lot of UI work.
*  Having a 'one to many' table to contain the age range selections.  Might not be that useful.

The first one is by far the easiest.  The problem lies if someone wants to change up the ranges that they're hard-coded.   Also, make sure there is a textbox that stores the current date, as two years from now these age ranges will mature, for lack of better word..

Good luck.
Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Generally age is always stored in a single column, and the fancy T-SQL is reserved for reports.

In your case, with those ranges, you could try this..

SELECT activity_name, case
   when age <= 7 then 'Ages 0-7'
   when age <= 12 then 'Ages 8 to 12'
   when age <= 17 then 'Ages 13 to 17'
   when age <= 20 then 'Ages 18 to 20'
   else '21 and over' end as age_ranges
FROM tblOrgActivities

I have an article called SQL Server CASE Solutionsthat illustrates this, and other CASE solutions.

>Since there can be more than one age group
You'll have to give some more detail as to how this works.
0
 
al4629740Author Commented:
I have a form in Visual Basic where you check off your age groups for the activity.  Since you can have more than one age group you can check off 0 to 7 and 8 to 12 for that activity.
0
 
al4629740Author Commented:
I would need to put an age range in and not an age
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.