Null Columns

[Avipatel]

Hi

Can someone help me with this query below.

Is there a way to list all columns in a table that contain Null Values. I have imported data into a new table but not all columns contain data. I need to write a query to list all the columns that have null values.

The result needs to give me a list of columns that do not have values in it. These need to be across the entire column ie if there is data in 1 row out of 1000 rows in column A, then the query should exclude this as it contains data.

I hope this is not too confusing but i am having trouble getting this query to work.

Avinash

 

[Guus2005]

select * from table1 where field1 is null

Enjoy!

 

[Dennisk]

you also need to count the number of records in the table so you can tell if a column has not been used. I've done exactly this somewere in the last 6 years

 

[Avipatel]

Hi

I have tried it but am not getting the correct result. eg below of what i am trying to achieve.

Basically, i got 1 table with 2 columns.

Table A

Column 1
Column 2

Column 1 has lots of data
Column 2 has no data

The query should show me only column 2 as it has no data in it.

Any help will be appreciated

Avinash

 

[neileg]

the Count() option in an aggregate query will exclude nulls unless you do Count(*). So you can compare the count of records with the count of a field. Any difference and you have nulls.

Or you could use DCount() instead of a query.

 

[Avipatel]

Thanks

The count option worked. I grouped all by count the result that = 0 is the columns that do not have any value in it. This is useful as i can maybe exclude these columns when importing from the same tables in the future.