Solved

setup age groups in a table

Posted on 2014-04-23
4
163 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
  • 2
  • 2
4 Comments
 
LVL 65

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 65

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

680 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