Need a help on Cross Tab type Situation

[navensg]

Hello all,



I have a data in table like below.

CE Name Cert A-01 Cert A-02 Cert A-04 Cert A-06 Cert A-07 GOES ON like this for another
AABB Yes Yes Yes No Yes
BBCC No Yes Yes No Yes
CCTT No No Yes No Yes


I want the data presented like below.
Certificate Name: Count
Cert A-01 120 (Count of Yes)
Cert A-02 120 (Count of Yes)
Cert A-03 160 (Count of Yes)
Cert A-04 58 (Count of Yes)
Cert A-05 130 (Count of Yes)
Cert A-01 170 (Count of Yes)
and Goes on....

What i have done so far. I have created query where using Iif formula, like if yes = 1 No = 0. From that query I grouped the data and now i am getting the count of Certificates. which looks like this.

Cert A-01 Cert A-02 Cert A-04 Cert A-06 Cert A-07 GOES ON like this for another 120........
120 120 160 58 130



But i how get this in horizontal way like i told before.

Please help me out guys...Thanks in advance.

Ng

[kennejd]

I want the data presented like below.
Certificate Name: Count
Cert A-01 120 (Count of Yes)
Cert A-02 120 (Count of Yes)
Cert A-03 160 (Count of Yes)
Cert A-04 58 (Count of Yes)
Cert A-05 130 (Count of Yes)
Cert A-01 170 (Count of Yes)
and Goes on....

You would have to write a union query....select CertA1, countOfYes from table where... union all select CertA2, countOfYes from table where... and goes on.

This is a good example of what happens when a database is not normalized...you get to jump through some hoops.