Crosstab SQL

[pickslides]

I have:

TRANSFORM Avg(SAP_CALD.[CASE_DAYS]) AS AvgOfCASE_DAYS
SELECT SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH], Avg(SAP_CALD.[CASE_DAYS]) AS [Total Of CASE_DAYS]
FROM SAP_CALD
GROUP BY SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH]
PIVOT SAP_CALD.[Model];

which works fine.

I want to look at the query SAP_CALD and disclude any record in the field 'department' that has a record of any {DPSG, System, Dealer, DealerIT, Inbound}

I can do this by piggybacking a another query that removes these but wondered if it would be easier to exclude them in the crosstab code.

Thanks, MQ..

 

[CJ_London]

just drag the department field to the field line, set the criteria and change group by to where

 

[pickslides]

Thanks,

I think that worked nicely.

Clicking on the SQLview now gives

TRANSFORM Avg(SAP_CALD.CASE_DAYS) AS AvgOfCASE_DAYS
SELECT SAP_CALD.OPN_YEAR, SAP_CALD.CLD_MONTH, Avg(SAP_CALD.CASE_DAYS) AS [Total Of CASE_DAYS]
FROM SAP_CALD
WHERE (((SAP_CALD.Department)<>"DPSG")) OR (((SAP_CALD.Department)<>"System")) OR (((SAP_CALD.Department)<>"Dealer")) OR (((SAP_CALD.Department)<>"Inbound")) OR (((SAP_CALD.Department)<>"DealerIT"))
GROUP BY SAP_CALD.OPN_YEAR, SAP_CALD.CLD_MONTH
PIVOT SAP_CALD.Model;

Thanks again, MQ

 

[CJ_London]

glad it works

you can simplify your criteria slightly to make ity easier to read using NOT IN i.e.

WHERE SAP_CALD.Department NOT IN ("DPSG,"System","Dealer","Inbound","DealerIT")