Solved

setup age groups in a table

Posted on 2014-04-23
4
165 Views
Last Modified: 2014-04-24
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

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40018882
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
 

Author Comment

by:al4629740
ID: 40018910
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
 

Author Comment

by:al4629740
ID: 40018914
I would need to put an age range in and not an age
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40018918
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question