Creating a two dimensional table (Crosstab type) in Access

[Ethel]

I have a table that lists about 165K email addresses and codes; i.e.

(e-mail address removed) 123
(e-mail address removed) 456
(e-mail address removed) 789
(e-mail address removed) 567
(e-mail address removed) 890

There are about 5,000 codes with over 100K unique email addresses

I want to creat a table that lists the email address and all the possible
codes next to it, i.e.

(e-mail address removed) 123 456 789
(e-mail address removed) 567 890

I expected a table with about 100K rows, one for each email addresses, and
then all their associated codes.

I have tried

TRANSFORM First(email_treatment.treatment_code) AS [first email]
SELECT email_treatment.email_address, email_treatment.treatment_code
FROM email_treatment
GROUP BY email_treatment.email_address
PIVOT email_treatment.treatment_code;

and I get and error saying too many column headers, but each email should
only have about 5 codes at most so there should only be at most 5 columns. I
think I am getting blank cells if a treatment is not associated with a
partular email address, but is used by another email address making more
columns than I need.

Any ideas?

 

[Michel Walsh]

Rank the second field, within the group defined by the first field, and
PIVOT on that rank.

If there is no duplicated value for the second field, for a given first
field, a relatively fast way to compute the rank can be:

SELECT a.f1, a.f2, COUNT(*) AS rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.f1=b.f1 AND a.f2 >= b.f2
GROUP BY a.f1, a.f2


then,

TRANSFORM LAST(f2)
SELECT f1
FROM q1
GROUP BY f1
PIVOT rank


Vanderghast, Access MVP