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
|
|||
|
|||
How to Union Crosstabs...
I've been reading many posts but still trying to comprehend the union of
crosstab queries. I need to take the weekly count of estimates that take under 4 days to be returned to the customer which I've calculated in the Under4Days crosstab and divide by Total Count by Week for a %toTotal by week. Here is the SQL for the two queries I need to combine. Also, when crosstabing, I noticed one of my 5 customers don't show any data for a week because the days taken were less than 4 that week. Is there a way to get results for each week even when the customer falls below the 4 day period? # 1 qryEstOperStats_Timely_Est_Diff_under4days_Count TRANSFORM Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under 4days.WORK_REQ_NO),0)) AS CountOfWORK_REQ_NO SELECT qryEstOperStats_Timely_Est_Diff_under4days.AREA, Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under 4days.WORK_REQ_NO),0)) AS [Total Of WORK_REQ_NO] FROM qryEstOperStats_Timely_Est_Diff_under4days GROUP BY qryEstOperStats_Timely_Est_Diff_under4days.AREA PIVOT Format([Week],"YYYY/MM/DD"); # 2 qryEstOperStats_Timely_Est_Diff_Count TRANSFORM Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff .WORK_REQ_NO),0)),0) AS CountOfWORK_REQ_NO SELECT qryEstOperStats_Timely_Est_Diff.AREA, Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff .WORK_REQ_NO),0)),0) AS [Total Of WORK_REQ_NO] FROM qryEstOperStats_Timely_Est_Diff GROUP BY qryEstOperStats_Timely_Est_Diff.AREA PIVOT Format([Week],"YYYY/MM/DD"); any help is appreciated! |
#2
|
|||
|
|||
How to Union Crosstabs...
Also, when crosstabing, I noticed one of my 5 customers don't show any data
for a week because the days taken were less than 4 that week. Is there a way to get results for each week even when the customer falls below the 4 day period? That is probably the root problem and also the solution! A union query requires the same number of fields in each query. One way to fix this problem is to open the crosstab query in design view; right click in the area near the tables; and select Properties. Next go into the Column Headings and put in something like: 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','S EP','OCT','NOV','DEC' The above should match the expected data. Advantages of Column Headings include sorting across as you want as the months above would normally do APR, AUG, etc. You can also make data not show up by taking out a column. For example, if you remove JAN then the January data won't show. And for your needs, it will create an empty field for the form or report even if there is no matching data. Then combine these with another query. For example: SELECT * from qryEstOperStats_Timely_Est_Diff_under4days_Count union all SELECT * from qryEstOperStats_Timely_Est_Diff_Count; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "dtoney" wrote: I've been reading many posts but still trying to comprehend the union of crosstab queries. I need to take the weekly count of estimates that take under 4 days to be returned to the customer which I've calculated in the Under4Days crosstab and divide by Total Count by Week for a %toTotal by week. Here is the SQL for the two queries I need to combine. Also, when crosstabing, I noticed one of my 5 customers don't show any data for a week because the days taken were less than 4 that week. Is there a way to get results for each week even when the customer falls below the 4 day period? # 1 qryEstOperStats_Timely_Est_Diff_under4days_Count TRANSFORM Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under 4days.WORK_REQ_NO),0)) AS CountOfWORK_REQ_NO SELECT qryEstOperStats_Timely_Est_Diff_under4days.AREA, Val(Nz(Count(qryEstOperStats_Timely_Est_Diff_under 4days.WORK_REQ_NO),0)) AS [Total Of WORK_REQ_NO] FROM qryEstOperStats_Timely_Est_Diff_under4days GROUP BY qryEstOperStats_Timely_Est_Diff_under4days.AREA PIVOT Format([Week],"YYYY/MM/DD"); # 2 qryEstOperStats_Timely_Est_Diff_Count TRANSFORM Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff .WORK_REQ_NO),0)),0) AS CountOfWORK_REQ_NO SELECT qryEstOperStats_Timely_Est_Diff.AREA, Round(Val(Nz(Count(qryEstOperStats_Timely_Est_Diff .WORK_REQ_NO),0)),0) AS [Total Of WORK_REQ_NO] FROM qryEstOperStats_Timely_Est_Diff GROUP BY qryEstOperStats_Timely_Est_Diff.AREA PIVOT Format([Week],"YYYY/MM/DD"); any help is appreciated! |
Thread Tools | |
Display Modes | |
|
|