OK, I believe the sql below should work for your two queries, note that I
made the column labels more general for the second in case you want to use
that as a base for future similar queries.
First query, revised original query without subtotals:
TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS
FROM (SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year,
Count([Start Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, Count([Start
Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null) AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;
Second Query, similar, but for Plant Install fields:
TRANSFORM Sum(qUS.FieldCount) AS SumOFieldCount
SELECT qUS.FieldName, Sum(qUS.FieldCount) AS FieldCount
FROM (SELECT [Start Up].[Plant Install] AS FieldName, [Start Up].Year,
Count([Start Up].[Plant Install]) AS FieldCount
FROM [Start Up]
GROUP BY [Start Up].[Plant Install], [Start Up].Year
HAVING [Start Up].[Plant Install] Is Not Null
UNION SELECT [Start Up].[Plant Install 2], [Start Up].Year, Count([Start
Up].[Plant Install 2])
FROM [Start Up]
GROUP BY [Start Up].[Plant Install 2], [Start Up].Year
HAVING [Start Up].[Plant Install 2] Is Not Null) AS qUS
GROUP BY qUS.FieldName
PIVOT qUS.Year;
Regarding the syntax error that you are getting, I think it is related to
the parenthesis surrounding the inner SELECT statement. Access often
converts these to square brackets, with a period after the closing square
bracket (this was the syntax used in Access 97, but later versions supposedly
use the ()'s, but still seem to convert to the square brackets). I'm not
sure exactly what is going on, but it appears to me that if you write or
paste the sql using parenthesis the first time, the query will work, even
after Access converts them to square brackets with a trailing dot. But, if
you write/paste the sql with the square brackets in the first place, Access
will not accept the syntax. Seems weird, but that's what seems to be
happening. When I changed the square brackets to ()'s in the sql you posted,
the query worked. Then, after closing and reopening, the sql had been
converted to square brackets, but continued to work.
If this gets to be a pain for you, you may want to save the inner select
statement (the union query) as a standalone query, and then use that as the
source for the crosstabs. If you want to do that, do the following:
Paste all sql text inside the ()'s or square brackets into a new query, add
a semi-colon at the end, and save.
For example, in the first query above, this would be:
SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year, Count([Start
Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, Count([Start
Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null;
For our example, lets say you saved this as Query1, your crosstab query
would then become:
TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS
FROM Query1 AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;
Note that all I did there was replace everything inside the parenthesis (and
the parenthesis as well) with the name of the union query. I kept the alias
for the union query, so that it wouldn't be necessary to change all of the
other field references.
Hope that helps. Post back and let me know how it goes.
-Ted Allen
Ted Allen said:
Hi Jesse,
Sorry it took so long to respond, I was out of the office all day. I have
to head out now, but it should only take a few minutes to revise the sql in
the morning to get rid of the subtotal fields. All you really have to do is
get rid of the two parts containing the abs() function, but if you don't need
those I can simplify the overall sql by getting rid of the type designation
as well.
I'll also look over the sql that you posted and test it on my end to see
what I can find.
-Ted
Jesse said:
If i wanted just the combined total and Years columns without the 2 subtotals
what would i have to take out. And I'm still getting that error when i try to
save the same query with different field names. Here is what will not save:
TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS, Abs(Sum((PSSType =
"PSS")*PSSCount)) AS SubTotalPSS1, Abs(Sum((PSSType = "PSS2")*PSSCount)) AS
SubTotalPSS2
FROM [SELECT [Start Up].[Plant Install] AS PSSName, [Start Up].Year, "PSS"
AS PSSType, Count([Start Up].[Plant Install]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Plant Install], [Start Up].Year, "PSS"
HAVING [Start Up].[Plant Install] Is Not Null
UNION SELECT [Start Up].[Plant Install 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Plant Install 2])
FROM [Start Up]
GROUP BY [Start Up].[Plant Install 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Plant Install 2] Is Not Null]. AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;
Its the same case except instead of Pre-Ship Setup its Plant Install. And it
still will not save the original sql when i try to make another copy of it.