|
| 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
June 27th, 2008, 07:17 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Eliminate 0 value in crosstab
I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
I then have a query to join them all together to see all informaition needed. The problem I am having is there are some 00 values that are causing my AVG's to total instead. I realize that this is a normalizing issue, but this data is coming off of mainframe and so it is what it is. So, if I can eliminate the zeros or tell the query to ignore them without eliminating the row that would be great. Any ideas? 
|
|
#2
June 27th, 2008, 07:28 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Eliminate 0 value in crosstab
Post your SQL.
-- KARL DEWEY Build a little - Test a little "Rbirdie" wrote: I have a data dump (over 110k info) that is now set up as 3 crosstab queries. I then have a query to join them all together to see all informaition needed. The problem I am having is there are some 00 values that are causing my AVG's to total instead. I realize that this is a normalizing issue, but this data is coming off of mainframe and so it is what it is. So, if I can eliminate the zeros or tell the query to ignore them without eliminating the row that would be great. Any ideas?
|
|
#3
June 27th, 2008, 07:49 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Eliminate 0 value in crosstab
SELECT DISTINCTROW Crosstab1.Model,
Avg((NZ(Crosstab2.Actual,0)+NZ(Crosstab2.COPIED,0) +NZ(Crosstab2.[HIGH PRIOR PRORATE],0)+NZ(Crosstab2.[Prorated From Actual],0))) AS [Total Count of Actuals], Sum((NZ(Crosstab1.Actual,0)+NZ(Crosstab1.COPIED,0) +NZ(Crosstab1.[HIGH PRIOR PRORATE],0)+NZ(Crosstab1.[Prorated From Actual],0))) AS [Avg of Actuals], Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual], NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0) AS [otal Count of Defaults], Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel, Crosstab3.[], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR FROM (Crosstab1 INNER JOIN Crosstab2 ON Crosstab1.Model = Crosstab2.Expr1) INNER JOIN Crosstab3 ON Crosstab2.Expr1 = Crosstab3.Model GROUP BY Crosstab1.Model, Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual], NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0), Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel, Crosstab3.[], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR, Crosstab2.[Prorated From Default], Crosstab2.UNKNOWN, Crosstab2.Actual, Crosstab2.COPIED, Crosstab2.[HIGH PRIOR PRORATE], Crosstab2.[Prorated From Actual]; "KARL DEWEY" wrote: Post your SQL. -- KARL DEWEY Build a little - Test a little "Rbirdie" wrote: I have a data dump (over 110k info) that is now set up as 3 crosstab queries. I then have a query to join them all together to see all informaition needed. The problem I am having is there are some 00 values that are causing my AVG's to total instead. I realize that this is a normalizing issue, but this data is coming off of mainframe and so it is what it is. So, if I can eliminate the zeros or tell the query to ignore them without eliminating the row that would be great. Any ideas?
|
|
#4
June 27th, 2008, 07:58 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Eliminate 0 value in crosstab
"KARL DEWEY" wrote: Post your SQL. -- KARL DEWEY Build a little - Test a little "Rbirdie" wrote: I have a data dump (over 110k info) that is now set up as 3 crosstab queries. I then have a query to join them all together to see all informaition needed. The problem I am having is there are some 00 values that are causing my AVG's to total instead. I realize that this is a normalizing issue, but this data is coming off of mainframe and so it is what it is. So, if I can eliminate the zeros or tell the query to ignore them without eliminating the row that would be great. Any ideas?
|
|
#5
June 27th, 2008, 07:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Eliminate 0 value in crosstab
"Rbirdie" wrote:
|
|
#6
June 27th, 2008, 08:34 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Eliminate 0 value in crosstab
Try replacing
NZ(Crosstab2.Actual,0) with IIf([Crosstab2].[Actual]=0,Null,[Crosstab2].[Actual]) -- KARL DEWEY Build a little - Test a little "Rbirdie" wrote: SELECT DISTINCTROW Crosstab1.Model, Avg((NZ(Crosstab2.Actual,0)+NZ(Crosstab2.COPIED,0) +NZ(Crosstab2.[HIGH PRIOR PRORATE],0)+NZ(Crosstab2.[Prorated From Actual],0))) AS [Total Count of Actuals], Sum((NZ(Crosstab1.Actual,0)+NZ(Crosstab1.COPIED,0) +NZ(Crosstab1.[HIGH PRIOR PRORATE],0)+NZ(Crosstab1.[Prorated From Actual],0))) AS [Avg of Actuals], Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual], NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0) AS [otal Count of Defaults], Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel, Crosstab3.[], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR FROM (Crosstab1 INNER JOIN Crosstab2 ON Crosstab1.Model = Crosstab2.Expr1) INNER JOIN Crosstab3 ON Crosstab2.Expr1 = Crosstab3.Model GROUP BY Crosstab1.Model, Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual], NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0), Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel, Crosstab3.[], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR, Crosstab2.[Prorated From Default], Crosstab2.UNKNOWN, Crosstab2.Actual, Crosstab2.COPIED, Crosstab2.[HIGH PRIOR PRORATE], Crosstab2.[Prorated From Actual]; "KARL DEWEY" wrote: Post your SQL. -- KARL DEWEY Build a little - Test a little "Rbirdie" wrote: I have a data dump (over 110k info) that is now set up as 3 crosstab queries. I then have a query to join them all together to see all informaition needed. The problem I am having is there are some 00 values that are causing my AVG's to total instead. I realize that this is a normalizing issue, but this data is coming off of mainframe and so it is what it is. So, if I can eliminate the zeros or tell the query to ignore them without eliminating the row that would be great. Any ideas?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
