How to merge data from different fields in a crosstab query

[Marouane]

Hi

I would like to get help in creating ranges in the fields of a crosstab.

The requirement is as follows:

I have a table that stores the birthdates of the customers as well as their
country of origin and I want to have a crosstab query that shows the ranges
of ages on the top like 15-20, 21-25, 26-30....and the country they are from
on the side.

Something like this:

Country | Total of e-mail | 15-20 | 21-25 | 26-30|

 

[John Spencer]

TRANSFORM Count(Email) as TheCount
SELECT Country
FROM SomeTable
WHERE DOB Is NOT NULL
GROUP BY Country
PIVOT Partition(Int(Format(Date(),"yyyy.mmdd") - Format(DOB,"yyyy.mmdd")),16,60,5)

Or if you want to use some other age calculation you can do so. To use the
partition function you need to have regular intervals, so I arbitrarily set
this up to use 16-20; 21-25; etc.

Another choice is to build a small table to contain the ranges and join the
age to the small table.
Table 1: A table with ages from 1 to 100 and a second field with the group
identifier.
Age: ID
15: 15-20
16: 15-20
....
21: 21-25

or Table 2: table with age ranges and third field with group identifier
Min: Max: ID
15 : 20 : 15-20
21 : 25 : 21-25

I would probably use the first table of ages from 0 to 100 since it would use
a standard equi-join (CalculatedAge = Age) instead of a non-equi join
(CalculatedAge >= Min and CalculatedAge <= Max) to get the Age range (ID)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County