Crosstab columns dynamic so I can't sum on them

Myriad_Rocker

Questioning Reality
Local time
Today, 18:53
Joined
Mar 26, 2004
Messages
166
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?
 
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.
 
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')
 
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
 
The "column headings" property in the QBE design view just creates the In clause I mentioned in the SQL for you. Same same. :-)
 
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!
 

Users who are viewing this thread

Back
Top Bottom