Arrggghhhh, Now I can't even copy and paste without blowing
it. There is an extra parenthesis in there.

Take ... I've lost count ;-):

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

Please, tell me that's a wrap ;-)
--
Marsh
MVP [MS Access]


Blair wrote:
you are getting closer qrysheds and qryTotal works
qrySubTotal has this error
Thanks Blair

syntax error (Comma) in query Expression Count(IIf(([Whelping Date] Is
Null,Null,[NEST #]))

TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]
"Marshall Barton" wrote
Blair wrote:

I want to apologize for the need for brackets.I hired a guy to do my db
and
I thought he was a genius.Then he took off and I had to learn how to do
it.Then I found these newsgroups and have since then with yawls help found
out all the things he did wrong, like the need for all those brackets and
non normalization of the tables.
But I am going to try and get someone to redo my db, now I have an idea
of
how it should be.

You have no idea how much I appreciate your help

I still get this error
syntax error (missing operator) in query '[Shed #]

UNION ALL
. . .


I need a vacation!

I skipped the last step in my tests. It seems that you can
not use UNION with a crosstab query. To get around that, we
need to create the three cross tab queries as separate saved
queries.

Really tricky set of queries, Take 3:

qrySheds:
TRANSFORM Count([NEST #]) AS [The Value]
SELECT [WHELPING DATE],
Count([NEST #]) AS [Total Of NEST #]
FROM QDailyWheplingReport2
GROUP BY [WHELPING DATE]
PIVOT [SHED #]

qrySubTotal:
TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
SELECT "SubTotal",
Count(IIf(([Whelping Date] Is Null,Null,[NEST #]))
FROM QDailyWheplingReport2
GROUP BY "SubTotal"
PIVOT [SHED #]

qryTotal:
TRANSFORM Count([NEST #])
SELECT "Total", Count([NEST #])
FROM QDailyWheplingReport2
GROUP BY "Total"
PIVOT [SHED #]

Finally, the query that puts it all together:
SELECT * FROM qrySheds
UNION ALL
SELECT * FROM qrySubTotal
UNION ALL
SELECT * FROM qryTotal