CrossTab Query problem

[Dueno]

Hi everybody, i hope someone can help me with this problem.

I've got a table which i need to make an overview with with the help of a crosstab query. There is 1 thing I cant get working:
this is the table:
Project Status Kleur Datum TM
CT79 Financials AMBER 11-4-2012 DHr tester
CT79 Financials GREEN 21-3-2012 DHr tester
CT79 Interd GREEN 11-4-2012 DHr tester
CT79 Interd GREEN 21-3-2012 DHr tester

I use this crosstab query:
TRANSFORM QryRAGNa.Kleur
SELECT QryRAGNa.Project, QryRAGNa.Status
FROM QryRAGNa
GROUP BY QryRAGNa.Project, QryRAGNa.Status, QryRAGNa.Kleur
PIVOT QryRAGNa.Datum;

The following is the result:
Project Status 11-4-2012 21-3-2012
CT79 Financials AMBER
CT79 Financials GREEN
CT79 Interd GREEN GREEN

What I expected to see was:
Project Status 11-4-2012 21-3-2012
CT79 Financials AMBER GREEN
CT79 Interd GREEN GREEN

So without the extra line...
How do i do that, it has been driving me mad all day
Thanks in advance!

 

[Dueno]

TRANSFORM First(QryRAGNa.Kleur) AS EersteVanKleur
SELECT QryRAGNa.Project, QryRAGNa.Status
FROM QryRAGNa
GROUP BY QryRAGNa.Project, QryRAGNa.Status
PIVOT QryRAGNa.Datum;

That solved the problem!

 

[robertbwainwright]

Now someone please correct me if I am wrong, but the column you are looking to use stop repeating is KLEUR and that is a text field. You would need to change that to value instead of row. This maybe the solution you are looking for (I named your table Test):

TRANSFORM First(test.Kleur) AS FirstOfKleur
SELECT test.Project, test.Status
FROM test
GROUP BY test.Project, test.Status
PIVOT test.Datum;

I used "First" (meaning the first value it the query finds it will use if I remember correctly). The problem will be that if you have CT79, interd on 4/11 with amber as well it will populate your crosstab with the first value it finds. This will not be a problem if you can only have CT79, interd can only be one kleur.

Hope that helps!

Robert