Crosstab query in subform does not hide blank columns

[Rob123]

Hi,



Due to the fact that Pivot Tables are no longer on newer versions of Access, I have converted my Pivot Table to crosstab queries based on prior valued advice given. The crosstab queries does indeed make my life a bit simpler, and easier to create a neat dashboard on an form, however it also show names of subordinates who no longer report directly to me. I was guided via a video tutorial to add all the names in the Column Heading properties field in the query design, due to the fact that the form does not work if it is not done. I do not want to manually enter and delete names each time out of this field i design view, as it might be that they will report back to me at a later stage.

Is there an easier way to handle this problem, so that my dashboard exclude the names automatically if there is no data.

As I understand, and experienced first hand, the crosstab query cannot be added to a form if the Column Heading properties field is not completed. I want to use a form, as I have combo boxes, which I use to filter and display the results of the crosstab query in a subform with the upon click (requery) event.

The attached .png images is the problem I am referring to.

I am open to any other alternatives as well, however VBA knowledge is extremely limited.

Kind regards,

Rob123

[Micron]

Not being privy to the other advice you were given I have to wonder why you need to see this in a spreadsheet format when you're working in a db? It's that sort of thinking that gets you into these situations. Having to redesign forms and reports to include/exclude fields on an on-going basis just seems wrong to me. Why can't names go vertically with the numbers beside them?

handle this problem, so that my dashboard exclude the names automatically if there is no data

This is exactly what I'm saying, and is how a database should work whenever possible. If your form is based on a query that excludes names you don't want, then voilà - you get what your question asks for.