Crosstab columns dynamic so I can't sum on them

[Myriad_Rocker]

I did a search on "if exists" and didn't come up with a solution for this problem. I have a crosstab query that takes the teams from a table and adds up all the instances of that particular team name. Red, Green, and Blue.

However, there are not always instances of all teams. So, sometimes the crosstab will return Red and Green but not Blue because no Blue's exist.

In a later query, I take this crosstab in as a source and try to add up a total and can't because Blue doesn't exist.

Any way I could get around this without creating a temp table and appending the results from the crosstab to it?

 

[HiTechCoach]

I use a dynamic method to handle this.

See the attached example:

 

[Myriad_Rocker]

I use a dynamic method to handle this.

See the attached example:

I'm sorry, I don't follow your example...is this a SQL solution?

 

[HiTechCoach]

I'm sorry, I don't follow your example...is this a SQL solution?

I have been using the example I post with database since 1995 with such great success that I have not every tried any other way.

I am sure there are other ways to get what you want.

Just a thought, you might be able to use a UNION query to add the totals.

 

[LPurvis]

You can require that the crosstab return certain specified fields by using the In clause in the SQL statement.
e.g.
PIVOT FieldName In ('Red', 'Green', 'Blue')

 

[DCrake]

Another way to force the columns is to go to properties and column headings and type in the reuqired column headings manually. This then gives you all the columns even if there is no data present.

David

 

[LPurvis]

The "column headings" property in the QBE design view just creates the In clause I mentioned in the SQL for you. Same same.

 

[Myriad_Rocker]

You can require that the crosstab return certain specified fields by using the In clause in the SQL statement.
e.g.
PIVOT FieldName In ('Red', 'Green', 'Blue')

THAT'S RIGHT!!!! I forgot all about that! Thanks!