how to create a crosstab query 1

[btamsgn]

Hi ACE,
I have a table 2 column as follows: dsname and NH
dsName NH
Á Bằng Kim
An Bình Moc
An Khang Kim
Ân Tuấn Thuy
Anh Đức Moc
Anh Duy Kim
......
I want to create a crosstab query as follows:

Pls find the attachment for your ref.
Thank you.

 

[dhookom]

I would first create a helper query that does a group by and counts to get the STT value which is in alpha order. I don't know where you get the STT values.

SELECT Count(T_Hs.NH) AS STT, T_Hs.dsName, T_Hs.NH
FROM T_Hs AS T_Hs_1 INNER JOIN T_Hs ON T_Hs_1.NH = T_Hs.NH
WHERE (((T_Hs.dsname)>=[t_hs_1].[dsName]))
GROUP BY T_Hs.dsName, T_Hs.NH;

Then use that query as the source for your crosstab:

TRANSFORM Min(qgrpForCrosstab.dsName) AS MinOfdsName
SELECT qgrpForCrosstab.STT
FROM qgrpForCrosstab
GROUP BY qgrpForCrosstab.STT
PIVOT qgrpForCrosstab.NH;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016