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