Count Across Columns

[Craig]

In a query,m I have selected four fields plus the id field from a table of
50.
Each of the records are marked with an X or not.


ID 2000 2002 2004 2006 Count ?
1 x x x 3
2 x x 2
3 x x 2
4 x x x x 4


How can I count the number of fields that are notnull accross the four
columns?
I am looking at 600,000 records

THanks
Craig

 

[Guest]

SELECT
Table1.ID,
IIf([Field1] Is Not Null,1,0)+
IIf([Field2] Is Not Null,1,0)+
IIf([Field3] Is Not Null,1,0)+
IIf([Field4] Is Not Null,1,0) AS NumerofNonNullFields

FROM Table1;




--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Craig]

Thankyou

Just what i needed.

Craig

SELECT
Table1.ID,
IIf([Field1] Is Not Null,1,0)+
IIf([Field2] Is Not Null,1,0)+
IIf([Field3] Is Not Null,1,0)+
IIf([Field4] Is Not Null,1,0) AS NumerofNonNullFields

FROM Table1;




--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

In a query,m I have selected four fields plus the id field from a table
of
50.
Each of the records are marked with an X or not.


ID 2000 2002 2004 2006 Count ?
1 x x x 3
2 x x 2
3 x x 2
4 x x x x 4


How can I count the number of fields that are notnull accross the four
columns?
I am looking at 600,000 records

THanks
Craig