Re: Calculating totals on a Crosstab Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <marshbarton@xxxxxxxxxx> wrote in message
news:4e11u25nl1n9upisvr3l0jfurgon02osvu@xxxxxxxxxx
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

--
Marsh
MVP [MS Access]


.

Relevant Pages

  • Re: Calculating totals on a Crosstab Query
    ... FROM QDailyWheplingReport2 ... PIVOT ... syntax error in query Expression Count(IIf((Is ...
    (microsoft.public.access.reports)
  • Re: Calculating totals on a Crosstab Query
    ... FROM QDailyWheplingReport2 ... PIVOT ... syntax error in query Expression Count(IIf((Is ...
    (microsoft.public.access.reports)
  • Re: Calculating totals on a Crosstab Query
    ... FROM QDailyWheplingReport2 ... PIVOT ... syntax error in query Expression Count(IIf((Is ...
    (microsoft.public.access.reports)
  • Re: Calculating totals on a Crosstab Query
    ... FROM QDailyWheplingReport2 ... PIVOT ... Then I get another syntax error in query ' ...
    (microsoft.public.access.reports)
  • Re: Please help ~ Syntax Error
    ... If this is not for a query, then you will need to change it to use ... The only time the error stops, see below, but then the report doesn't ... So perhaps I should try another approach, maybe my unbound form wasn't ... Syntax error in query expression '(Between ...
    (microsoft.public.access.formscoding)