Yet Another Query Prob

[Ashfaque]

Hi,

My this query I created is presenting department wize employees including all nationalities.

TRANSFORM Count(T_JobOffer.[CDept]) AS CountOfCDept
SELECT T_JobOffer.[CDept]
FROM T_JobOffer
GROUP BY T_JobOffer.[CDept]
ORDER BY T_JobOffer.[CDept]
PIVOT T_JobOffer.[CNationality];

There is a dept called "Top Management" which I dont want to appear in my graph therefore I was modifying the above query using <>"Top Management" but it says "You can't specify on the same field for which you entered value in the cross tab row"

Any help?

 

[arnelgp]

TRANSFORM Count(T_JobOffer.[CDept]) AS CountOfCDept
SELECT T_JobOffer.[CDept]
FROM T_JobOffer
WHERE Nz([Cdept], "Top Management") <> "Top Management"
GROUP BY T_JobOffer.[CDept]
ORDER BY T_JobOffer.[CDept]
PIVOT T_JobOffer.[CNationality];

 

[Ashfaque]

Msg appearing "You can't assign a value to this object" and removing the modified code keeping the old one

 

[Pat Hartman]

I don't know why arnel's solution doesn't work but you can create a select query that eliminates the unwanted value and use that querydef instead of the table in you CrossTab query.

 

[Ashfaque]

Hi Pat,

I tried to replace and made new SELECT query. But attached msg is appearing

SELECT A.CDept, Count(A.CNationality) AS CountOfCNationality
FROM A
GROUP BY A.CDept
HAVING (((A.CDept)<>"Top Management"))
ORDER BY A.CDept;

 

[arnelgp]

try to delete/re-create the chart.

 

[Ashfaque]

I did it Arnel.

This is 3rd time I did that

 

[Ashfaque]

This time it is working....let me check if there are still some errors.....