Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query problem

Status
Not open for further replies.

glich

Technical User
Joined
Sep 26, 2001
Messages
4
Location
AU
i have calculated the ages of the members form there date of birth using datediff("yyyy",[date of birth],date())

what i want to do is display an list of activties down the left side and have column headings like 21 to 30years
31 to 40 years and so on

i need to display the number of members in each age bracket in there selected activities
 
Here is a sample solution...

I have the following tables...

PERSON
Name BirthDate
=============================
BigBird 7/8/1970
Frankenstein 6/8/1965
TweetleDee 9/2/1971
TweetleDum 10/24/1961

PERSONACTIVITY
Name Activity
=============================
BigBird Run
BigBird Push-Ups
BigBird TricepPush
BigBird Sit-ups

Next, I created a query that merges the information...

SELECT PersonActivity.PersonName, PersonActivity.PersonActivity, Person.BirthDate
FROM Person INNER JOIN PersonActivity ON Person.Person = PersonActivity.PersonName;

Now that the information is prepared, I created the following query...

SELECT ALLPersons.PersonActivity, IIf(Year(Now())-Year([Birthdate]) Between 19 And 37,1,0) AS [20 through 36], IIf(Year(Now())-Year([Birthdate])>36,1,0) AS [36 and Up]
FROM ALLPersons;

To complete the grouping and count them accordingly, I created the last query.

SELECT AllPersonsOverTime.PersonActivity, Sum(AllPersonsOverTime.[20 through 36]) as [20 through 36], Sum(AllPersonsOverTime.[36 and Up]) as [36 and Above]
FROM AllPersonsOverTime
GROUP BY PersonActivity;

You should be able to modify this to accomodate your age range. I tried doing this through a cross-tab query, but couldn't obtain the ranges. If someone knows an easier way, by all means provide an alternative. In fact, I may have an idea that will simplify it even further, but I am out of time.

Gary
gwinn7
 
There is an error. I actually have more data in PERSONACTIVITY than what was stated in my example.

Gary
gwinn7
 
Thanks for the help i did try a croos tab query but failed
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top