Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waltersmx2
Starting Member
4 Posts |
 Posted - 2004-05-26 : 11:23:32
|
| I am creating a cross tab query where some of the fields show up as blanks. I am trying to do a calculated field based on the values in this cross tab query. There are six columns and I am trying to add three of the six together. Where there is a blank field, it does not add the columns even if there is information in the other two fields. I think this is because the field is blank instad of having a zero value. How do I get the cross tab query to show a zero value instead of a blank? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-26 : 11:59:27
|
| Wrap an isnull() around the calculation... |
 |
|
|
waltersmx2
Starting Member
4 Posts |
Posted - 2004-05-26 : 12:08:41
|
| I am new at Access and I must not be doing this right. My field names in the cross tab query are AGE1, AGE2, AGE3, AGE4, AGE5, AGE6. My calculated field I entered into the field name OVER120:isnull([AGE4]+[AGE5]+[AGE6]).My first line in the query has information in all three fields for AGE4 - AGE6 and the result in the calculated field is now 0. In the second line AGE4 and AGE5 are blank and information is only in AGE6. The result in that calculated field is now a -1.What am I doing wrong?If this helps, this is my cross tab query:TRANSFORM Sum([HMO Aging download].AR_BALANCE) AS SumOfAR_BALANCESELECT [HMO Aging download].HOSPITAL_ID, [HMO Aging download].INSURANCE_PLAN, Sum([HMO Aging download].AR_BALANCE) AS [Total Of AR_BALANCE]FROM [HMO Aging download]GROUP BY [HMO Aging download].HOSPITAL_ID, [HMO Aging download].INSURANCE_PLANPIVOT [HMO Aging download].AGE_GROUP;From there I am creating a new table to include the calculated field:SELECT [HMO Aging Dollars].HOSPITAL_ID, [HMO Aging Dollars].INSURANCE_PLAN, [HMO Aging Dollars].AGE1, [HMO Aging Dollars].AGE2, [HMO Aging Dollars].AGE3, [HMO Aging Dollars].AGE4, [HMO Aging Dollars].AGE5, [HMO Aging Dollars].AGE6, [HMO Aging Dollars].[Total Of AR_BALANCE], isnull([AGE4]+[AGE5]+[AGE6]) AS Over120FROM [HMO Aging Dollars]; |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-05-26 : 20:05:45
|
| You'll need to use the IsNull (or the Access equivalent Nz()) for each of the fields: ..... (Nz(AGE4,0)+Nz(AGE5,0)+Nz(AGE6,0)) as Over120.Access's IsNull is not the same as SQL Server's. It will return true or false, depending on if the value is a null. Nz will substitute a value if the field is null.Tim |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-27 : 05:48:59
|
Whoops, sorry, must have been asleep at the wheel... |
|
|
|
|
|
|
|
|
Access Cross Tab Query