Unanswered: Keeping queried columns with no results
In one of my queries, if there are no results the columns vanish. I read something about nz function and Value if Null, but cant's seem to get it to work right. All I am trying to do ensure that regardless of results, ALL columns stay in the query when ran. ( see screen print)
Can anyone show me what to put in my criteria to make this happen.
In order to do this, my approach has always been to create a dummy table with one record with zeroes in all the columns. Use a UNION ALL query to add the record to your live data and then do the cross tab. Then if you need to, filter out the dummy data.
The Nz function will give you zeroes for all your nulls so you can sum the data.
Will you always have the same columns in the crosstab query, unless one drops out? If so, another way to be sure a column does not drop out would be to add the column names into the Column Headings property of the crosstab query. with the query open in design view, right click anywhere in the upper portion of the query, but not on a table, then select properties. The third property down is the Column Headings. In your case you would add "In Progress", "Resarching", "Left Message", etc. Then, even if one of those columns does not contain any data, the column will be in the query output.