Solved

setup age groups in a table

Posted on 2014-04-23
4
160 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now