Access SQL delete empty columns

Question

Hello everyone,

Recently, I've been using Microsoft Access a lot. Usually, if I had a question, a quick Google search was enough. However, this time is different. I need to delete a couple of empty columns from a table. By this, I mean that I want Access to show all columns, except for the ones that are empty for every row. Here's an example:

Name	Prop1	Prop2	Prop3
A	1	2
B	2	1
C		2	1
D	1

Then, after running this simple script:

SELECT * FROM Table WHERE Prop1 IS NOT NULL

It'll result in this table:

Name	Prop1	Prop2	Prop3
A	1	2
B	2	1
D	1

However, I have quite a lot of columns, so after running this script, I have a LOT of empty columns left that make it really hard to see the relevant information. So how do I remove these useless columns and get the following table:

Name	Prop1	Prop2
A	1	2
B	2	1
D	1

Answer 1

I wouldn't use the word "delete" if I were you, unless you actually want data to be deleted from the table.  If I understand you correctly, what you really want to do is only return those columns  where that column is not null in the entire result set.  If I'm wrong, disregard the rest of this post.

A SQL SELECT statement can only return a fixed set of columns, so there's no way to do this with a simple query alone.  There are only two ways I can think of to do this, both requiring code to achieve the result:

1. First query the table (behind the scenes) for the records matching the criteria, then examine those records to see which columns are Null in all records, and then build a new SELECT statement applying the same criteria, requesting just those columns for which were not all Null.  Then stuff that SELECT statement into a stored query (DAO querydef) and open it as a datasheet.
2. Go ahead and open a datasheet on a query that returns all the columns, but after doing so, use code to examine all the columns and hide those that are entirely Null.  If the query returns a large number of records, this process may take noticeable time, so you'd probably want to hide the query datasheet initially (opening it hidden), and only make it visible when all columns have been examined, and hidden if necessary.

I would have to experiment to see which of these approaches worked fastest.  It seems to me that option 1 could take advantage of any indexes on the table, while option 2 could not.  On the other hand, option 2 may be simpler to implement.

Answer 2

I'm not sure a completely understand your question because your final table still include an empty field.

That all said, you can add an Is Not Null criteria to each column so in the end you will only be returned those records with values in every field.

SELECT * 

FROM Table 

WHERE ((Prop1 IS NOT NULL) AND (Prop2 IS NOT NULL) AND (Prop3 IS NOT NULL) AND (Prop4 IS NOT NULL));

Answer 3

Hi ENDRNL,

Did you refer to the replies above?

Regards,

Joanne

Answer 4

A point here. you don't "Delete" columns. So to restate your question it sounds like you want to filter out all records from your query where all columns are empty. 

If that's what you want, then Daniel's query will do the job. Just include the Is Not Null criteria for each column in the query.