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
|
|||
|
|||
Using IIF to return a zero value for a null
Hi,
This is not so much a question as a sharing of something I've come across. It won't be earthshattering nor epiphanous, but it might prove helpful. I have some queries of the following form: SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode frequency]) AS id017 FROM [tipcodes 2003] WHERE ((([tipcodes 2003].[tipcode]) In (164,172))) GROUP BY [tipcodes 2003].[event name]; which will return the sum of the "tipcode frequencies" for "tipcodes" 164 and 172, sorted by "event name". The table "tipcodes 2003" lists all tipcodes which occurred with a nonzero frequency, i.e. that result occurred at all, sorted by event name. This works perfectly fine. However, if I build another query like: SELECT [tipcodes 2003].[event name], recruit101.id101 AS [No Contact], recruit102.id102 AS [Contacted/Not Eligible], recruit103.id103 AS [Contacted/Eligible], recruit104.id104 AS Overquota, recruit105.id105 AS [Total Recruited], [No Contact]+[Contacted/Not Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total Sample] FROM ((((([tipcodes 2003] LEFT JOIN recruit101 ON [tipcodes 2003].[event name] = recruit101.[event name]) LEFT JOIN recruit102 ON [tipcodes 2003].[event name] = recruit102.[event name]) LEFT JOIN recruit103 ON [tipcodes 2003].[event name] = recruit103.[event name]) LEFT JOIN recruit104 ON [tipcodes 2003].[event name] = recruit104.[event name]) LEFT JOIN recruit105 ON [tipcodes 2003].[event name] = recruit105.[event name]) LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] = [Total Sample (Recruit)].[event name] GROUP BY [tipcodes 2003].[event name], recruit101.id101, recruit102.id102, recruit103.id103, recruit104.id104, recruit105.id105, [Total Sample]; ....I run into an interesting problem. For those events which did not happen to have any tipcodes in the "Overquota" category, for instance, there is no record in the "recruit104.id104" query. When the query above runs into such a situation, it returns a null for that event in the "Overquota" column. This, in turn, makes the "[Total Sample]" calculated field return null. As you can imagine, this is something less than helpful. There are a few alternatives, each with a varying degree of utility for the labor: (1) Rebuild the table(s) to include a reference to each tipcode, and assign a value of zero to those not included in the original data file. (2) Create a query "Total Sample" which sums the appropriate tipcode frequencies for all events, thereby driving this directly from the data and not from calculated fields based on other queries. I actually produced this result, but was a bit bullish about the calculated field if for no other reason than to serve as a proof of concept. (3) Use the following expression: IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104]) This returns a zero in the "Overquota" column in the above query for an event name not listed in "id104". I'm not sure of which method is more practical or more wholesome from the purist's view. I don't think I need to refer to the tipcodes in two separate places, though, especially since the sets can get a bit cumbersome to manage. It seemed better to build the queries at the lowest level of detail I might eventually want to analyze, and then simply compile these results with calculations. Any thoughts? |
#2
|
|||
|
|||
Using IIF to return a zero value for a null
look in # error in calculation dd 25 may?
"Muraii" wrote in message m... Hi, This is not so much a question as a sharing of something I've come across. It won't be earthshattering nor epiphanous, but it might prove helpful. I have some queries of the following form: SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode frequency]) AS id017 FROM [tipcodes 2003] WHERE ((([tipcodes 2003].[tipcode]) In (164,172))) GROUP BY [tipcodes 2003].[event name]; which will return the sum of the "tipcode frequencies" for "tipcodes" 164 and 172, sorted by "event name". The table "tipcodes 2003" lists all tipcodes which occurred with a nonzero frequency, i.e. that result occurred at all, sorted by event name. This works perfectly fine. However, if I build another query like: SELECT [tipcodes 2003].[event name], recruit101.id101 AS [No Contact], recruit102.id102 AS [Contacted/Not Eligible], recruit103.id103 AS [Contacted/Eligible], recruit104.id104 AS Overquota, recruit105.id105 AS [Total Recruited], [No Contact]+[Contacted/Not Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total Sample] FROM ((((([tipcodes 2003] LEFT JOIN recruit101 ON [tipcodes 2003].[event name] = recruit101.[event name]) LEFT JOIN recruit102 ON [tipcodes 2003].[event name] = recruit102.[event name]) LEFT JOIN recruit103 ON [tipcodes 2003].[event name] = recruit103.[event name]) LEFT JOIN recruit104 ON [tipcodes 2003].[event name] = recruit104.[event name]) LEFT JOIN recruit105 ON [tipcodes 2003].[event name] = recruit105.[event name]) LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] = [Total Sample (Recruit)].[event name] GROUP BY [tipcodes 2003].[event name], recruit101.id101, recruit102.id102, recruit103.id103, recruit104.id104, recruit105.id105, [Total Sample]; ...I run into an interesting problem. For those events which did not happen to have any tipcodes in the "Overquota" category, for instance, there is no record in the "recruit104.id104" query. When the query above runs into such a situation, it returns a null for that event in the "Overquota" column. This, in turn, makes the "[Total Sample]" calculated field return null. As you can imagine, this is something less than helpful. There are a few alternatives, each with a varying degree of utility for the labor: (1) Rebuild the table(s) to include a reference to each tipcode, and assign a value of zero to those not included in the original data file. (2) Create a query "Total Sample" which sums the appropriate tipcode frequencies for all events, thereby driving this directly from the data and not from calculated fields based on other queries. I actually produced this result, but was a bit bullish about the calculated field if for no other reason than to serve as a proof of concept. (3) Use the following expression: IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104]) This returns a zero in the "Overquota" column in the above query for an event name not listed in "id104". I'm not sure of which method is more practical or more wholesome from the purist's view. I don't think I need to refer to the tipcodes in two separate places, though, especially since the sets can get a bit cumbersome to manage. It seemed better to build the queries at the lowest level of detail I might eventually want to analyze, and then simply compile these results with calculations. Any thoughts? |
#3
|
|||
|
|||
Using IIF to return a zero value for a null
Check Access VB Help on the Nz() function which you can use to convert Null
to zero. I am not aware that you can use Aliases of other Fields in the Calculated Field [Total Sample]??? -- HTH Van T. Dinh MVP (Access) "Muraii" wrote in message m... Hi, This is not so much a question as a sharing of something I've come across. It won't be earthshattering nor epiphanous, but it might prove helpful. I have some queries of the following form: SELECT [tipcodes 2003].[event name], Sum([tipcodes 2003]![tipcode frequency]) AS id017 FROM [tipcodes 2003] WHERE ((([tipcodes 2003].[tipcode]) In (164,172))) GROUP BY [tipcodes 2003].[event name]; which will return the sum of the "tipcode frequencies" for "tipcodes" 164 and 172, sorted by "event name". The table "tipcodes 2003" lists all tipcodes which occurred with a nonzero frequency, i.e. that result occurred at all, sorted by event name. This works perfectly fine. However, if I build another query like: SELECT [tipcodes 2003].[event name], recruit101.id101 AS [No Contact], recruit102.id102 AS [Contacted/Not Eligible], recruit103.id103 AS [Contacted/Eligible], recruit104.id104 AS Overquota, recruit105.id105 AS [Total Recruited], [No Contact]+[Contacted/Not Eligible]+[Contacted/Eligible]+[Overquota]+[Total Recruited] AS [Total Sample] FROM ((((([tipcodes 2003] LEFT JOIN recruit101 ON [tipcodes 2003].[event name] = recruit101.[event name]) LEFT JOIN recruit102 ON [tipcodes 2003].[event name] = recruit102.[event name]) LEFT JOIN recruit103 ON [tipcodes 2003].[event name] = recruit103.[event name]) LEFT JOIN recruit104 ON [tipcodes 2003].[event name] = recruit104.[event name]) LEFT JOIN recruit105 ON [tipcodes 2003].[event name] = recruit105.[event name]) LEFT JOIN [Total Sample (Recruit)] ON [tipcodes 2003].[event name] = [Total Sample (Recruit)].[event name] GROUP BY [tipcodes 2003].[event name], recruit101.id101, recruit102.id102, recruit103.id103, recruit104.id104, recruit105.id105, [Total Sample]; ...I run into an interesting problem. For those events which did not happen to have any tipcodes in the "Overquota" category, for instance, there is no record in the "recruit104.id104" query. When the query above runs into such a situation, it returns a null for that event in the "Overquota" column. This, in turn, makes the "[Total Sample]" calculated field return null. As you can imagine, this is something less than helpful. There are a few alternatives, each with a varying degree of utility for the labor: (1) Rebuild the table(s) to include a reference to each tipcode, and assign a value of zero to those not included in the original data file. (2) Create a query "Total Sample" which sums the appropriate tipcode frequencies for all events, thereby driving this directly from the data and not from calculated fields based on other queries. I actually produced this result, but was a bit bullish about the calculated field if for no other reason than to serve as a proof of concept. (3) Use the following expression: IIF(IsNull([recruit104]![id104]),0,[recruit104]![id104]) This returns a zero in the "Overquota" column in the above query for an event name not listed in "id104". I'm not sure of which method is more practical or more wholesome from the purist's view. I don't think I need to refer to the tipcodes in two separate places, though, especially since the sets can get a bit cumbersome to manage. It seemed better to build the queries at the lowest level of detail I might eventually want to analyze, and then simply compile these results with calculations. Any thoughts? |
Thread Tools | |
Display Modes | |
|
|