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 |
#31
|
|||
|
|||
Calculating totals on a Crosstab Query
IF you like I could send you a db with just qrys and records you need to try
it out Thanks Blair This is the first qry, the one the crosstabs are built off. Qry name is QDailyWhelpingReport 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]![MatingYear]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); Next is the first crosstab you sent me, Qry name is QDailyWhelpingReport_Shed TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value] SELECT QDailyWhelpingReport.[WHELPING DATE], Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #] FROM QDailyWhelpingReport GROUP BY QDailyWhelpingReport.[WHELPING DATE] PIVOT QDailyWhelpingReport.[SHED #]; Next The 2nd Crosstab you sent me Qry name is QDailyWhelpingReport_SubTotal TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1 SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr3 FROM QDailyWhelpingReport GROUP BY "SubTotal" PIVOT QDailyWhelpingReport.[SHED #]; Next The 3rd Crosstab you sent me Qry name is QDailyWhelpingReport_Total TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #] SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #1] FROM QDailyWhelpingReport GROUP BY "Total" PIVOT QDailyWhelpingReport.[SHED #]; Next The 4th Crosstab you sent me Qry name is QDailyWhelpingReport_UnionQry SELECT * FROM QDailyWhelpingReport_Shed UNION ALL SELECT * FROM QDailyWhelpingReport_SubTotal UNION ALL SELECT * FROM QDailyWhelpingReport_Total; "Marshall Barton" wrote in message ... Crosstab queries generally require their parameters to be declared, so that part is necessary. I need to see the queries involved before I can hope to understand the rest of what you're describing. Sometimes you can unravel parameter issues by temporarily repacing the parameter with a literal value. If that works, then the problem is in the way the parameter value is referenced or on the source of the parameter value. If the literal value doesn't work either, then the problem is in how the parameter is used. -- Marsh MVP [MS Access] SBGFF wrote: The data type in the table is set to Number.Just got your last post. To night I deleted every thing and started from scratch.Made a new qry and every thing works till I try to open the first crosstab you made, then it has an error concerning the parameter. I tried declaring the parameter in the query, it then opened but there were no results. I tried declaring them in the qry your qry's are based on and I don't get any results, remove them and the qry works. remove them from the crosstab and I get the error. Something ain't just quite right. In the parameters data type in the qry, number is not there, the only option that it will let me use is Date/time "Marshall Barton" wrote Check the data type of the [Mating Year] field in the table. If it's a Text field, the text box on the form shuld probably be set to: CStr(Year(Date())) I don't understand why you should need to change your system's date. If you want to search for a different year, can't you just type the year in the text box? I am afraid that there is so much going on now that I have lost track of what you are now using for the query and what's in the form. Blair wrote: Using Year(Now()) in the form load with the option to change it if I wanted a different years results. Tried it in the text box default value and it did the same thing. It seems like the qry and form has some hidden link, besides the obvious. I do have several of copies until I get what I want.I will have to start exporting to another db to hold them till I don't need them "Marshall Barton" wrote I don't see where you are using Date(), Now() or whatever it is that's dependent on the computer's date. I think I need to be kept abreast of the changes to the form and the query. The names I suggested that you change are the names of the controls on the form. The control references in the query must then be modified to agree with the names on the form. It sounds like you are having trouble keeping them in sync, possibly because you have too many copies of the form and query Blair wrote: Something is terribly screwy with something or my db. If I want to bring up 2006 year records I have to change my computer Date to 2006 in order for the query to show any records. If I make a new qry it will work properly in retrieving the records, But then the qry's you made won't work. They come back with the error that it can't find Text6 or MatingYear, which is what I called it in the new qry as you suggested. Any Idea what's going on here?? Weird!! "Marshall Barton" wrote Blair wrote: Found the problem, but can't fix it Tracked it down to the name change of my form that has the controls and the query that has the criteria. When I change them it won't work, even though it is all spelled correctly, it won't recognize it.I change it back and it will work. 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. 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)); Keep checking the names. The Text6 and Option4 are likely to be different if you rereated the form. I strongly suggest that you use more meaningful names such as txtMatingYear and that you also avoid names with spaces and/or other non-alphanumeric characters. Another potential issue is the criteria: ((Dead Like Forms!FDailyWhelpingReport!Option4) Or (.Dead=IsNull(Forms!FDailyWhelpingReport!Option4)) Note the use of parenthesis to explicitly specify the evaluation order of precedence between the AND and OR ecpressions. The issue is that Like is only appropriate when comparing a Text field to a wildcard pattern. I think you can get the intended result from: Dead = Nz(Forms!FDailyWhelpingReport!Option4, True) |
#32
|
|||
|
|||
Calculating totals on a Crosstab Query
The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and every thing works till I try to open the first crosstab you made, then it has an error concerning the parameter. I tried declaring the parameter in the query, it then opened but there were no results. I tried declaring them in the qry your qry's are based on and I don't get any results, remove them and the qry works. remove them from the crosstab and I get the error. Something ain't just quite right. In the parameters data type in the qry, number is not there, the only option that it will let me use is Date/time Thanks for any help you can give me Blair "Marshall Barton" wrote in message ... Check the data type of the [Mating Year] field in the table. If it's a Text field, the text box on the form shuld probably be set to: CStr(Year(Date())) I don't understand why you should need to change your system's date. If you want to search for a different year, can't you just type the year in the text box? I am afraid that there is so much going on now that I have lost track of what you are now using for the query and what's in the form. -- Marsh MVP [MS Access] Blair wrote: Using Year(Now()) in the form load with the option to change it if I wanted a different years results. Tried it in the text box default value and it did the same thing. It seems like the qry and form has some hidden link, besides the obvious. I do have several of copies until I get what I want.I will have to start exporting to another db to hold them till I don't need them "Marshall Barton" wrote I don't see where you are using Date(), Now() or whatever it is that's dependent on the computer's date. I think I need to be kept abreast of the changes to the form and the query. The names I suggested that you change are the names of the controls on the form. The control references in the query must then be modified to agree with the names on the form. It sounds like you are having trouble keeping them in sync, possibly because you have too many copies of the form and query Blair wrote: Something is terribly screwy with something or my db. If I want to bring up 2006 year records I have to change my computer Date to 2006 in order for the query to show any records. If I make a new qry it will work properly in retrieving the records, But then the qry's you made won't work. They come back with the error that it can't find Text6 or MatingYear, which is what I called it in the new qry as you suggested. Any Idea what's going on here?? Weird!! "Marshall Barton" wrote Blair wrote: Found the problem, but can't fix it Tracked it down to the name change of my form that has the controls and the query that has the criteria. When I change them it won't work, even though it is all spelled correctly, it won't recognize it.I change it back and it will work. 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. 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)); Keep checking the names. The Text6 and Option4 are likely to be different if you rereated the form. I strongly suggest that you use more meaningful names such as txtMatingYear and that you also avoid names with spaces and/or other non-alphanumeric characters. Another potential issue is the criteria: ((Dead Like Forms!FDailyWhelpingReport!Option4) Or (.Dead=IsNull(Forms!FDailyWhelpingReport!Option4)) Note the use of parenthesis to explicitly specify the evaluation order of precedence between the AND and OR ecpressions. The issue is that Like is only appropriate when comparing a Text field to a wildcard pattern. I think you can get the intended result from: Dead = Nz(Forms!FDailyWhelpingReport!Option4, True) |
#33
|
|||
|
|||
Calculating totals on a Crosstab Query
Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the queries involved before I can hope to understand the rest of what you're describing. Sometimes you can unravel parameter issues by temporarily repacing the parameter with a literal value. If that works, then the problem is in the way the parameter value is referenced or on the source of the parameter value. If the literal value doesn't work either, then the problem is in how the parameter is used. -- Marsh MVP [MS Access] SBGFF wrote: The data type in the table is set to Number.Just got your last post. To night I deleted every thing and started from scratch.Made a new qry and every thing works till I try to open the first crosstab you made, then it has an error concerning the parameter. I tried declaring the parameter in the query, it then opened but there were no results. I tried declaring them in the qry your qry's are based on and I don't get any results, remove them and the qry works. remove them from the crosstab and I get the error. Something ain't just quite right. In the parameters data type in the qry, number is not there, the only option that it will let me use is Date/time "Marshall Barton" wrote Check the data type of the [Mating Year] field in the table. If it's a Text field, the text box on the form shuld probably be set to: CStr(Year(Date())) I don't understand why you should need to change your system's date. If you want to search for a different year, can't you just type the year in the text box? I am afraid that there is so much going on now that I have lost track of what you are now using for the query and what's in the form. Blair wrote: Using Year(Now()) in the form load with the option to change it if I wanted a different years results. Tried it in the text box default value and it did the same thing. It seems like the qry and form has some hidden link, besides the obvious. I do have several of copies until I get what I want.I will have to start exporting to another db to hold them till I don't need them "Marshall Barton" wrote I don't see where you are using Date(), Now() or whatever it is that's dependent on the computer's date. I think I need to be kept abreast of the changes to the form and the query. The names I suggested that you change are the names of the controls on the form. The control references in the query must then be modified to agree with the names on the form. It sounds like you are having trouble keeping them in sync, possibly because you have too many copies of the form and query Blair wrote: Something is terribly screwy with something or my db. If I want to bring up 2006 year records I have to change my computer Date to 2006 in order for the query to show any records. If I make a new qry it will work properly in retrieving the records, But then the qry's you made won't work. They come back with the error that it can't find Text6 or MatingYear, which is what I called it in the new qry as you suggested. Any Idea what's going on here?? Weird!! "Marshall Barton" wrote Blair wrote: Found the problem, but can't fix it Tracked it down to the name change of my form that has the controls and the query that has the criteria. When I change them it won't work, even though it is all spelled correctly, it won't recognize it.I change it back and it will work. 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. 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)); Keep checking the names. The Text6 and Option4 are likely to be different if you rereated the form. I strongly suggest that you use more meaningful names such as txtMatingYear and that you also avoid names with spaces and/or other non-alphanumeric characters. Another potential issue is the criteria: ((Dead Like Forms!FDailyWhelpingReport!Option4) Or (.Dead=IsNull(Forms!FDailyWhelpingReport!Option4)) Note the use of parenthesis to explicitly specify the evaluation order of precedence between the AND and OR ecpressions. The issue is that Like is only appropriate when comparing a Text field to a wildcard pattern. I think you can get the intended result from: Dead = Nz(Forms!FDailyWhelpingReport!Option4, True) |
#34
|
|||
|
|||
Calculating totals on a Crosstab Query
sending again cause had date set for 3/2/2007 for testing db, don't know if
it makes any difference in you getting it IF you like I could send you a db with just qrys and records you need to try it out Thanks Blair This is the first qry, the one the crosstabs are built off. Qry name is QDailyWhelpingReport 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]![MatingYear]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); Next is the first crosstab you sent me, Qry name is QDailyWhelpingReport_Shed TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value] SELECT QDailyWhelpingReport.[WHELPING DATE], Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #] FROM QDailyWhelpingReport GROUP BY QDailyWhelpingReport.[WHELPING DATE] PIVOT QDailyWhelpingReport.[SHED #]; Next The 2nd Crosstab you sent me Qry name is QDailyWhelpingReport_SubTotal TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1 SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr3 FROM QDailyWhelpingReport GROUP BY "SubTotal" PIVOT QDailyWhelpingReport.[SHED #]; Next The 3rd Crosstab you sent me Qry name is QDailyWhelpingReport_Total TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #] SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #1] FROM QDailyWhelpingReport GROUP BY "Total" PIVOT QDailyWhelpingReport.[SHED #]; Next The 4th Crosstab you sent me Qry name is QDailyWhelpingReport_UnionQry SELECT * FROM QDailyWhelpingReport_Shed UNION ALL SELECT * FROM QDailyWhelpingReport_SubTotal UNION ALL SELECT * FROM QDailyWhelpingReport_Total; "Marshall Barton" wrote in message ... Crosstab queries generally require their parameters to be declared, so that part is necessary. I need to see the queries involved before I can hope to understand the rest of what you're describing. Sometimes you can unravel parameter issues by temporarily repacing the parameter with a literal value. If that works, then the problem is in the way the parameter value is referenced or on the source of the parameter value. If the literal value doesn't work either, then the problem is in how the parameter is used. -- Marsh MVP [MS Access] SBGFF wrote: The data type in the table is set to Number.Just got your last post. To night I deleted every thing and started from scratch.Made a new qry and every thing works till I try to open the first crosstab you made, then it has an error concerning the parameter. I tried declaring the parameter in the query, it then opened but there were no results. I tried declaring them in the qry your qry's are based on and I don't get any results, remove them and the qry works. remove them from the crosstab and I get the error. Something ain't just quite right. In the parameters data type in the qry, number is not there, the only option that it will let me use is Date/time "Marshall Barton" wrote Check the data type of the [Mating Year] field in the table. If it's a Text field, the text box on the form shuld probably be set to: CStr(Year(Date())) I don't understand why you should need to change your system's date. If you want to search for a different year, can't you just type the year in the text box? I am afraid that there is so much going on now that I have lost track of what you are now using for the query and what's in the form. Blair wrote: Using Year(Now()) in the form load with the option to change it if I wanted a different years results. Tried it in the text box default value and it did the same thing. It seems like the qry and form has some hidden link, besides the obvious. I do have several of copies until I get what I want.I will have to start exporting to another db to hold them till I don't need them "Marshall Barton" wrote I don't see where you are using Date(), Now() or whatever it is that's dependent on the computer's date. I think I need to be kept abreast of the changes to the form and the query. The names I suggested that you change are the names of the controls on the form. The control references in the query must then be modified to agree with the names on the form. It sounds like you are having trouble keeping them in sync, possibly because you have too many copies of the form and query Blair wrote: Something is terribly screwy with something or my db. If I want to bring up 2006 year records I have to change my computer Date to 2006 in order for the query to show any records. If I make a new qry it will work properly in retrieving the records, But then the qry's you made won't work. They come back with the error that it can't find Text6 or MatingYear, which is what I called it in the new qry as you suggested. Any Idea what's going on here?? Weird!! "Marshall Barton" wrote Blair wrote: Found the problem, but can't fix it Tracked it down to the name change of my form that has the controls and the query that has the criteria. When I change them it won't work, even though it is all spelled correctly, it won't recognize it.I change it back and it will work. 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. 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)); Keep checking the names. The Text6 and Option4 are likely to be different if you rereated the form. I strongly suggest that you use more meaningful names such as txtMatingYear and that you also avoid names with spaces and/or other non-alphanumeric characters. Another potential issue is the criteria: ((Dead Like Forms!FDailyWhelpingReport!Option4) Or (.Dead=IsNull(Forms!FDailyWhelpingReport!Option4)) Note the use of parenthesis to explicitly specify the evaluation order of precedence between the AND and OR ecpressions. The issue is that Like is only appropriate when comparing a Text field to a wildcard pattern. I think you can get the intended result from: Dead = Nz(Forms!FDailyWhelpingReport!Option4, True) |
#35
|
|||
|
|||
Calculating totals on a Crosstab Query
As always, double check the names of the controls on the
form to make sure they are identical to the names you use in the query parameters. I don't see where you explained which error message you received and/or which parameter was causing it. Your first query's WHERE clause is not quite right. Don't use Like unless you really are doing a wildcard text comparison. WHERE [Mating Year] = Forms!FDailyWhelpingReport!MatingYear AND (Dead = Forms!FDailyWhelpingReport!OptionDead Or (Dead=IsNull(Forms!FDailyWhelpingReport!OptionDead ) AND [1st MATING] Is Not Null The Dead test looks odd, the way it is written, the query will return only records with Dead = True when you do not specify OptionDead on the form. If that still gives you trouble, remove the criteria one at a time to see if it helps you locate the one that causing trouble. If you can deal with it, you can avoid all those extra [ ], ( ), and table names by never switching the query from SQL view to the QBE grid. When you save a query from SQL view, it will come back in SQL view. -- Marsh MVP [MS Access] SBGFF wrote: IF you like I could send you a db with just qrys and records you need to try it out This is the first qry, the one the crosstabs are built off. Qry name is QDailyWhelpingReport 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]![MatingYear]) AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead] Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead])) AND (([98MatingRecords].[1st MATING]) Is Not Null)); Next is the first crosstab you sent me, Qry name is QDailyWhelpingReport_Shed TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value] SELECT QDailyWhelpingReport.[WHELPING DATE], Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #] FROM QDailyWhelpingReport GROUP BY QDailyWhelpingReport.[WHELPING DATE] PIVOT QDailyWhelpingReport.[SHED #]; Next The 2nd Crosstab you sent me Qry name is QDailyWhelpingReport_SubTotal TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1 SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr3 FROM QDailyWhelpingReport GROUP BY "SubTotal" PIVOT QDailyWhelpingReport.[SHED #]; Next The 3rd Crosstab you sent me Qry name is QDailyWhelpingReport_Total TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #] SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #1] FROM QDailyWhelpingReport GROUP BY "Total" PIVOT QDailyWhelpingReport.[SHED #]; Next The 4th Crosstab you sent me Qry name is QDailyWhelpingReport_UnionQry SELECT * FROM QDailyWhelpingReport_Shed UNION ALL SELECT * FROM QDailyWhelpingReport_SubTotal UNION ALL SELECT * FROM QDailyWhelpingReport_Total; "Marshall Barton" wrote Crosstab queries generally require their parameters to be declared, so that part is necessary. I need to see the queries involved before I can hope to understand the rest of what you're describing. Sometimes you can unravel parameter issues by temporarily repacing the parameter with a literal value. If that works, then the problem is in the way the parameter value is referenced or on the source of the parameter value. If the literal value doesn't work either, then the problem is in how the parameter is used. SBGFF wrote: The data type in the table is set to Number.Just got your last post. To night I deleted every thing and started from scratch.Made a new qry and every thing works till I try to open the first crosstab you made, then it has an error concerning the parameter. I tried declaring the parameter in the query, it then opened but there were no results. I tried declaring them in the qry your qry's are based on and I don't get any results, remove them and the qry works. remove them from the crosstab and I get the error. Something ain't just quite right. In the parameters data type in the qry, number is not there, the only option that it will let me use is Date/time |
Thread Tools | |
Display Modes | |
|
|