This may be a bit verbose...
I have a series of crosstab queries that are unioned together to produce the final output. The union is made up of 4 crosstab queries and work fine for all but two situations. In the two situations, the number of cols created are 92 with the col headings spelling out Country Names. When the union query is run, the error message is "Too many fields combined". The help message indicated that there were more than 255 fields and to reduce the number of fields. When I change the union query to only run 2 of the 4 crosstabs, it runs without error. Not sure how the query is determining the 255 limit because in all of the queries the max number of cols seems to be 92. If by the number of fields the union query is counting each set of 92 into the total 255, then I could understand why it fails, yet, if that is the case, I would expect my other unions to fail also because they have 72 cols (72*4=288), however they work perfectly. I did get the result I needed by creating 3 queries (1-Union of crosstabs 1 and 2, 2-Union of 2 crosstabs 3 and 4, 3-Union of Union1 and Union2) which oddly enough works. It would be nice to know why it works with the unions in 3 queries rather than in one and also if there is a better way of getting the results needed as even in the case where the single union works, it still takes a total of 5 queries to get the desired output since it didn't look like I could directly do a union of the crosstabs.
Here is one set of queries
[tt]
TRANSFORM Sum(ACC)
SELECT 2, Fall, LVL, "Accepted" AS Status, Sum(ACC)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(APP) AS SumOfAPP
SELECT 1 AS SortOrder, Fall, LVL, "Applicants" AS Status, Sum(APP) AS Total
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(DEP)
SELECT 4, Fall, lvl, "Deposited" AS Status, Sum(DEP)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(REG)
SELECT 5, Fall, lvl, "Enrolled" AS Status, Sum(REG)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
SELECT *
FROM qryAPP2004LST_F_App
UNION
SELECT *
FROM qryAPP2004LST_F_Acc
UNION
SELECT *
FROM qryAPP2004LST_F_Dep
UNION SELECT *
FROM qryAPP2004LST_F_Reg
ORDER BY 3, 2, 1;
[/tt]
Sample Output from the Union Query
[tt]
StOrder Fall LVL Status Total 0Intl/Other AE AK
1 2004 F Applicants 5 3 1 1
2 2004 F Accepted 6 2 0 4
4 2004 F Deposited
5 2004 F Enrolled
1 2005 F Applicants
2 2005 F Accepted
4 2005 F Deposited
5 2005 F Enrolled
1 2006 F Applicants
2 2006 F Accepted
4 2006 F Deposited ... ... ... ... ...
5 2006 F Enrolled
1 2004 T Applicants
2 2004 T Accepted
4 2004 T Deposited
5 2004 T Enrolled
1 2005 T Applicants
2 2005 T Accepted
4 2005 T Deposited
5 2005 T Enrolled
1 2006 T Applicants
2 2006 T Accepted
4 2006 T Deposited
5 2006 T Enrolled 9 0 6 3
[/tt]
I have a series of crosstab queries that are unioned together to produce the final output. The union is made up of 4 crosstab queries and work fine for all but two situations. In the two situations, the number of cols created are 92 with the col headings spelling out Country Names. When the union query is run, the error message is "Too many fields combined". The help message indicated that there were more than 255 fields and to reduce the number of fields. When I change the union query to only run 2 of the 4 crosstabs, it runs without error. Not sure how the query is determining the 255 limit because in all of the queries the max number of cols seems to be 92. If by the number of fields the union query is counting each set of 92 into the total 255, then I could understand why it fails, yet, if that is the case, I would expect my other unions to fail also because they have 72 cols (72*4=288), however they work perfectly. I did get the result I needed by creating 3 queries (1-Union of crosstabs 1 and 2, 2-Union of 2 crosstabs 3 and 4, 3-Union of Union1 and Union2) which oddly enough works. It would be nice to know why it works with the unions in 3 queries rather than in one and also if there is a better way of getting the results needed as even in the case where the single union works, it still takes a total of 5 queries to get the desired output since it didn't look like I could directly do a union of the crosstabs.
Here is one set of queries
[tt]
TRANSFORM Sum(ACC)
SELECT 2, Fall, LVL, "Accepted" AS Status, Sum(ACC)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(APP) AS SumOfAPP
SELECT 1 AS SortOrder, Fall, LVL, "Applicants" AS Status, Sum(APP) AS Total
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(DEP)
SELECT 4, Fall, lvl, "Deposited" AS Status, Sum(DEP)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
TRANSFORM Sum(REG)
SELECT 5, Fall, lvl, "Enrolled" AS Status, Sum(REG)
FROM [APP-2004-LST]
WHERE (lvl="F" Or lvl="T") AND College <> "PS"
GROUP BY 1, Fall, lvl
ORDER BY 1, Fall, lvl
PIVOT IIf([cntry]="US",[State],"0Intl/Other");
SELECT *
FROM qryAPP2004LST_F_App
UNION
SELECT *
FROM qryAPP2004LST_F_Acc
UNION
SELECT *
FROM qryAPP2004LST_F_Dep
UNION SELECT *
FROM qryAPP2004LST_F_Reg
ORDER BY 3, 2, 1;
[/tt]
Sample Output from the Union Query
[tt]
StOrder Fall LVL Status Total 0Intl/Other AE AK
1 2004 F Applicants 5 3 1 1
2 2004 F Accepted 6 2 0 4
4 2004 F Deposited
5 2004 F Enrolled
1 2005 F Applicants
2 2005 F Accepted
4 2005 F Deposited
5 2005 F Enrolled
1 2006 F Applicants
2 2006 F Accepted
4 2006 F Deposited ... ... ... ... ...
5 2006 F Enrolled
1 2004 T Applicants
2 2004 T Accepted
4 2004 T Deposited
5 2004 T Enrolled
1 2005 T Applicants
2 2005 T Accepted
4 2005 T Deposited
5 2005 T Enrolled
1 2006 T Applicants
2 2006 T Accepted
4 2006 T Deposited
5 2006 T Enrolled 9 0 6 3
[/tt]
