Query Based on A CrossTab

[houseofturner]

I have a cross tab query which produces a set of results for me which analyses the status of sales opportunities. So for example it would show:

Region , Manager, Sales Person as columns

Open, Closed, Won, Lost etc as headings.

I then have queries referencing the cross tab to produce a variety of summary of figures for reports.

The issue I have is that if I change the date range for the Cross Tab, it might mean there are no "Won" records (for example), which means that the summary queries, which use the Cross Tab as a base, refuse to run because they are looking for a field which no longer exists.

Any help would be much appreciated.

 

[MarkK]

Yes, this is an inescapable feature of crosstabs, that the resulting columns are not known until runtime. This is powerful option on the one hand, but introduces the vulnerability that you can't rely on the presence of those columns. What I do is leave my crosstab as the final query. I think you'll need to redesign your process to not rely on a crosstab query as an intermediate processing step.