If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating totals on a Crosstab Query
Blair wrote:
I get a syntax error (missing operator) in query expression 'count(.[Nest #])'. I removed the dot in front of [Nest #] Then I get another syntax error (missing operator) in query '[Shed #] Man, did I make a mess of that or what. You think I can blame it on all those square brackets you had to use because of the funky characters in those field names? ;-) Guess not. It's probably because my eyes aren't good enought to see all those tiny little dots. I shouldn't feel too bad though, you only found one of them ;-) Tricky query, take two: TRANSFORM Count([NEST #]) AS [The Value] SELECT [WHELPING DATE], Count([NEST #]) AS [Total Of NEST #] FROM QDailyWheplingReport2 GROUP BY [WHELPING DATE] PIVOT [SHED #] UNION ALL TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) SELECT Null, Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] UNION ALL TRANSFORM Count([NEST #]) SELECT Null, Count([NEST #]) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] -- Marsh MVP [MS Access] |
#12
|
|||
|
|||
Calculating totals on a Crosstab Query
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 Thanks Blair I still get this error syntax error (missing operator) in query '[Shed #] UNION ALL TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) SELECT Null, Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] UNION ALL TRANSFORM Count([NEST #]) SELECT Null, Count([NEST #]) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] "Marshall Barton" wrote in message ... Blair wrote: I get a syntax error (missing operator) in query expression 'count(.[Nest #])'. I removed the dot in front of [Nest #] Then I get another syntax error (missing operator) in query '[Shed #] Man, did I make a mess of that or what. You think I can blame it on all those square brackets you had to use because of the funky characters in those field names? ;-) Guess not. It's probably because my eyes aren't good enought to see all those tiny little dots. I shouldn't feel too bad though, you only found one of them ;-) Tricky query, take two: TRANSFORM Count([NEST #]) AS [The Value] SELECT [WHELPING DATE], Count([NEST #]) AS [Total Of NEST #] FROM QDailyWheplingReport2 GROUP BY [WHELPING DATE] PIVOT [SHED #] UNION ALL TRANSFORM Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) SELECT Null, Count(IIf(([Whelping Date] Is Null,Null,[NEST #])) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] UNION ALL TRANSFORM Count([NEST #]) SELECT Null, Count([NEST #]) FROM QDailyWheplingReport2 GROUP BY Null PIVOT [SHED #] -- Marsh MVP [MS Access] |
#13
|
|||
|
|||
Calculating totals on a Crosstab Query
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] |
#14
|
|||
|
|||
Calculating totals on a Crosstab Query
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 in message ... 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] |
#15
|
|||
|
|||
Calculating totals on a Crosstab Query
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 |
#16
|
|||
|
|||
Calculating totals on a Crosstab Query
BINGO!!!!!!
Thanks ever so much Blair "Marshall Barton" wrote in message ... 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 |
#17
|
|||
|
|||
Calculating totals on a Crosstab Query
Hi!
This has to do with the queries you wrote for me but not that they don't work because they do. I noticed in our communications that I had spelled WhelpingReport2 wrong. I spelled it Whepling instead of Whelping. My point is I went in and changed the names ( spelled them correctly) and I am sure I have changed every thing. I even went to the extent to make a whole new query where the criteria is controlled in a form. with that form open and I try to open your qryshed, it tells me the jet engine doesn't recognize it as a valid field. Do you have any idea what's going on, or will I have to start from scratch. something is not quite right. Thanks Blair "Marshall Barton" wrote in message ... 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 |
#18
|
|||
|
|||
Calculating totals on a Crosstab Query
It actually says
The Microsoft Jet database engine does not recognize '[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or expression. This is the sql of the qry QDailyWhelpingReport That I am using. I guess I should have used this from the start, but I was using the other until I got the results I wanted so I wouldn't have to mess with the criteria until I got it working the way I wanted. Sorry for the incontinence Thanks Blair SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #], [98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year], [98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead, [98MatingRecords].[1st MATING] FROM 98MatingRecords WHERE ((([98MatingRecords].[Mating Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![Option4] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); "Marshall Barton" wrote in message ... 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 |
#19
|
|||
|
|||
Calculating totals on a Crosstab Query
Blair wrote:
It actually says The Microsoft Jet database engine does not recognize '[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or expression. This is the sql of the qry QDailyWhelpingReport That I am using. I guess I should have used this from the start, but I was using the other until I got the results I wanted so I wouldn't have to mess with the criteria until I got it working the way I wanted. Sorry for the incontinence Thanks Blair SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #], [98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year], [98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead, [98MatingRecords].[1st MATING] FROM 98MatingRecords WHERE ((([98MatingRecords].[Mating Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![Option4] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); First, make sure that you have UNCHECKed the troublesome Name AutoCorrect feature (Tools menu - Options, General tab). This feature seems to randomly cause strange problems. Of more immediate importance, when you change the name of something, it changes the name of other things with the same name. Then double check the names of everything in the query, especially the name of the form. (I note that you changed the spelling of whelp , but you also seem to have dropped the 2 at the end.) -- Marsh MVP [MS Access] |
#20
|
|||
|
|||
Calculating totals on a Crosstab Query
I can't find Name AutoCorrect feature, Im using A97 if that makes a
difference. I'm pretty sure I have got all the names changed, but I will check again.The 2 on the end was for a copy to play with like we did till I got it working. I have gone to the extent of making a whole new query so the names would be right, it still didn't work. Gona go check and do it all over again Thanks Blair "Marshall Barton" wrote in message ... Blair wrote: It actually says The Microsoft Jet database engine does not recognize '[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or expression. This is the sql of the qry QDailyWhelpingReport That I am using. I guess I should have used this from the start, but I was using the other until I got the results I wanted so I wouldn't have to mess with the criteria until I got it working the way I wanted. Sorry for the incontinence Thanks Blair SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #], [98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year], [98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead, [98MatingRecords].[1st MATING] FROM 98MatingRecords WHERE ((([98MatingRecords].[Mating Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![Option4] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); First, make sure that you have UNCHECKed the troublesome Name AutoCorrect feature (Tools menu - Options, General tab). This feature seems to randomly cause strange problems. Of more immediate importance, when you change the name of something, it changes the name of other things with the same name. Then double check the names of everything in the query, especially the name of the form. (I note that you changed the spelling of whelp , but you also seem to have dropped the 2 at the end.) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|