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 |
#1
|
|||
|
|||
Adding Calcs to a Crosstab? Or Form?
I have finally figured out my first crosstab query and it does about half of
what I need it to do. I get a table that I have put into a form which displays the number of Enforcement Subtypes per Enforcement Category for each month. There is then a total number of Inspections at the far right per catgory. I still need a total for each month along the bottom (as this would be the total number of Inspections for the month) and I need each Quarter to total as well. Ie: Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4 Wellsite Low Risk 1 5 3 9 9 I have tried a number of different things that just haven't worked and I suspect that I just can't add them to the crosstab so, I have tried adding calculations on to the form but I am obviously not really getting it. Because the months come up in the query as 1, 2, 3.....I have tried even just adding them in an expression but it doesn't recognize the numbers. Any advice would be greatly appreciated. TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS CountOfInspection_Date SELECT Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category, Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date] FROM Qry_Count_Inspections_2 GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category PIVOT Qry_Count_Inspections_2.Month; Additionally, I know that the query currently only goes up to 6 for the months because there have been no entries in the other months. I put fields on my form for 7 - 12 because I would rather not have to edit the form every month as there are multiple users using their own front ends. Of course each of these months comes up with #Name? I can probably get away with explaining to the users that until an inspection date is entered for a month, this is how it will appear but it doesn't look very nice. Is there any way to make these displays 0's too? Thanks so much! |
#2
|
|||
|
|||
Adding Calcs to a Crosstab? Or Form?
To do all that you ask you need two crosstab queries - one for monthly and
another for quarters. Use this as you PIVOT in the monthly crosstab query -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); For the quarterly use this -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q"); To get your monthly and quarterly totals at the bottom do it in a report footer. -- KARL DEWEY Build a little - Test a little "carriey" wrote: I have finally figured out my first crosstab query and it does about half of what I need it to do. I get a table that I have put into a form which displays the number of Enforcement Subtypes per Enforcement Category for each month. There is then a total number of Inspections at the far right per catgory. I still need a total for each month along the bottom (as this would be the total number of Inspections for the month) and I need each Quarter to total as well. Ie: Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4 Wellsite Low Risk 1 5 3 9 9 I have tried a number of different things that just haven't worked and I suspect that I just can't add them to the crosstab so, I have tried adding calculations on to the form but I am obviously not really getting it. Because the months come up in the query as 1, 2, 3.....I have tried even just adding them in an expression but it doesn't recognize the numbers. Any advice would be greatly appreciated. TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS CountOfInspection_Date SELECT Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category, Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date] FROM Qry_Count_Inspections_2 GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category PIVOT Qry_Count_Inspections_2.Month; Additionally, I know that the query currently only goes up to 6 for the months because there have been no entries in the other months. I put fields on my form for 7 - 12 because I would rather not have to edit the form every month as there are multiple users using their own front ends. Of course each of these months comes up with #Name? I can probably get away with explaining to the users that until an inspection date is entered for a month, this is how it will appear but it doesn't look very nice. Is there any way to make these displays 0's too? Thanks so much! |
#3
|
|||
|
|||
Adding Calcs to a Crosstab? Or Form?
Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would
have figured out this Pivot Statement though! The only problem is that when I try to run it, I now get a Syntax Error in my Transform Statement. Any idea how to correct that? "KARL DEWEY" wrote: To do all that you ask you need two crosstab queries - one for monthly and another for quarters. Use this as you PIVOT in the monthly crosstab query -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); For the quarterly use this -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q"); To get your monthly and quarterly totals at the bottom do it in a report footer. -- KARL DEWEY Build a little - Test a little "carriey" wrote: I have finally figured out my first crosstab query and it does about half of what I need it to do. I get a table that I have put into a form which displays the number of Enforcement Subtypes per Enforcement Category for each month. There is then a total number of Inspections at the far right per catgory. I still need a total for each month along the bottom (as this would be the total number of Inspections for the month) and I need each Quarter to total as well. Ie: Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4 Wellsite Low Risk 1 5 3 9 9 I have tried a number of different things that just haven't worked and I suspect that I just can't add them to the crosstab so, I have tried adding calculations on to the form but I am obviously not really getting it. Because the months come up in the query as 1, 2, 3.....I have tried even just adding them in an expression but it doesn't recognize the numbers. Any advice would be greatly appreciated. TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS CountOfInspection_Date SELECT Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category, Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date] FROM Qry_Count_Inspections_2 GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category PIVOT Qry_Count_Inspections_2.Month; Additionally, I know that the query currently only goes up to 6 for the months because there have been no entries in the other months. I put fields on my form for 7 - 12 because I would rather not have to edit the form every month as there are multiple users using their own front ends. Of course each of these months comes up with #Name? I can probably get away with explaining to the users that until an inspection date is entered for a month, this is how it will appear but it doesn't look very nice. Is there any way to make these displays 0's too? Thanks so much! |
#4
|
|||
|
|||
Adding Calcs to a Crosstab? Or Form?
I ran it and found an extra open bracket in the PIVOT.
What is the exact error message? -- KARL DEWEY Build a little - Test a little "carriey" wrote: Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would have figured out this Pivot Statement though! The only problem is that when I try to run it, I now get a Syntax Error in my Transform Statement. Any idea how to correct that? "KARL DEWEY" wrote: To do all that you ask you need two crosstab queries - one for monthly and another for quarters. Use this as you PIVOT in the monthly crosstab query -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); For the quarterly use this -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q"); To get your monthly and quarterly totals at the bottom do it in a report footer. -- KARL DEWEY Build a little - Test a little "carriey" wrote: I have finally figured out my first crosstab query and it does about half of what I need it to do. I get a table that I have put into a form which displays the number of Enforcement Subtypes per Enforcement Category for each month. There is then a total number of Inspections at the far right per catgory. I still need a total for each month along the bottom (as this would be the total number of Inspections for the month) and I need each Quarter to total as well. Ie: Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4 Wellsite Low Risk 1 5 3 9 9 I have tried a number of different things that just haven't worked and I suspect that I just can't add them to the crosstab so, I have tried adding calculations on to the form but I am obviously not really getting it. Because the months come up in the query as 1, 2, 3.....I have tried even just adding them in an expression but it doesn't recognize the numbers. Any advice would be greatly appreciated. TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS CountOfInspection_Date SELECT Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category, Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date] FROM Qry_Count_Inspections_2 GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category PIVOT Qry_Count_Inspections_2.Month; Additionally, I know that the query currently only goes up to 6 for the months because there have been no entries in the other months. I put fields on my form for 7 - 12 because I would rather not have to edit the form every month as there are multiple users using their own front ends. Of course each of these months comes up with #Name? I can probably get away with explaining to the users that until an inspection date is entered for a month, this is how it will appear but it doesn't look very nice. Is there any way to make these displays 0's too? Thanks so much! |
#5
|
|||
|
|||
Adding Calcs to a Crosstab? Or Form?
Me Again - I retyped it from scratch and no more syntax error so maybe I had
the extra bracket too. This is exactly what I was looking for. Thanks so much!!! "KARL DEWEY" wrote: I ran it and found an extra open bracket in the PIVOT. What is the exact error message? -- KARL DEWEY Build a little - Test a little "carriey" wrote: Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would have figured out this Pivot Statement though! The only problem is that when I try to run it, I now get a Syntax Error in my Transform Statement. Any idea how to correct that? "KARL DEWEY" wrote: To do all that you ask you need two crosstab queries - one for monthly and another for quarters. Use this as you PIVOT in the monthly crosstab query -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); For the quarterly use this -- PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q"); To get your monthly and quarterly totals at the bottom do it in a report footer. -- KARL DEWEY Build a little - Test a little "carriey" wrote: I have finally figured out my first crosstab query and it does about half of what I need it to do. I get a table that I have put into a form which displays the number of Enforcement Subtypes per Enforcement Category for each month. There is then a total number of Inspections at the far right per catgory. I still need a total for each month along the bottom (as this would be the total number of Inspections for the month) and I need each Quarter to total as well. Ie: Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4 Wellsite Low Risk 1 5 3 9 9 I have tried a number of different things that just haven't worked and I suspect that I just can't add them to the crosstab so, I have tried adding calculations on to the form but I am obviously not really getting it. Because the months come up in the query as 1, 2, 3.....I have tried even just adding them in an expression but it doesn't recognize the numbers. Any advice would be greatly appreciated. TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS CountOfInspection_Date SELECT Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category, Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date] FROM Qry_Count_Inspections_2 GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype, Qry_Count_Inspections_2.Enf_Category PIVOT Qry_Count_Inspections_2.Month; Additionally, I know that the query currently only goes up to 6 for the months because there have been no entries in the other months. I put fields on my form for 7 - 12 because I would rather not have to edit the form every month as there are multiple users using their own front ends. Of course each of these months comes up with #Name? I can probably get away with explaining to the users that until an inspection date is entered for a month, this is how it will appear but it doesn't look very nice. Is there any way to make these displays 0's too? Thanks so much! |
Thread Tools | |
Display Modes | |
|
|