query problem

[glich]

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

 

[gwinn7]

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

 

[gwinn7]

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

Gary
gwinn7

 

[glich]

Thanks for the help i did try a croos tab query but failed