Forming Crosstab Query to Show 0 Data Fields

[bobbobruns12]

Hi,

I have a crosstab query which counts the number of exams based on day and dept. I am running code to automatically send it to excel however I need to create an array that will not change, depending on whether certain depts have data one week and not the next. I created a "dictionary" to store all of the necessary depts and need a way to display them all in the query, whether or not they have data for that week. Here is my sql query as created, and I need a function which can display all of the depts despite no data for the week. Thanks

TRANSFORM Count(IDXRAD_RFB_WEEKSTATS.XACN) AS CountOfXACN
SELECT IDXRAD_RFB_WEEKSTATS.XDEPT, Resource_Map.Resource
FROM Resource_Map INNER JOIN IDXRAD_RFB_WEEKSTATS ON (IDXRAD_RFB_WEEKSTATS.XRSC = Resource_Map.XRSC) AND (Resource_Map.XDEPT = IDXRAD_RFB_WEEKSTATS.XDEPT)
GROUP BY IDXRAD_RFB_WEEKSTATS.XDEPT, Resource_Map.Resource
PIVOT IDXRAD_RFB_WEEKSTATS.XDATE;

 

[lespaul]

If you have a table of all the departments you can use a LEFT or RIGHT join and include those with missing information in the other table.

Check out the Understanding SQL Joins link below for more information.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi