Queries: How do I select only the columns that have values?

[Remaniak]

Hi All,

I am using Access 2003 and I want to show in my query all columns from a table that have values. Its probably a simple question but I have not been able to crack it so far.

My code is below, but it won't give me any of my columns.

SELECT IIf([Data table].[ID]= Not Null,"[Data table].[ID]) AS ID",IIf([Data table].[Attribute 1]= Not Null,"[Data table].[Attribute 1] AS Attribute_1",""))
FROM [Data table];

Thanks a lot!

Remaniak

[MMcCarthy]

Try this ...

SELECT [ID], [Attribute 1]
FROM [Data table]
WHERE [ID] Is Not Null
AND [Attribute 1] Is Not Null;
 

[Remaniak]

Try this ...

SELECT [ID], [Attribute 1]
FROM [Data table]
WHERE [ID] Is Not Null
AND [Attribute 1] Is Not Null;
 

Hi McCarthy,

Thanks for your reply, but this will only give me all rows where Attribute and ID are both empty. But I want the query to skip the column if all of its rows are empty.

Would that be possible as well?

[NeoPa]

No I'm afraid not.
There is no facility in SQL to determine columns dynamically.

[MMcCarthy]

Hi McCarthy,

Thanks for your reply, but this will only give me all rows where Attribute and ID are both empty. But I want the query to skip the column if all of its rows are empty.

Would that be possible as well?

This should be returning records where neither column is empty.

Mary

[Remaniak]

This should be returning records where neither column is empty.

Mary

Hi Mary,

You were right, it returns everthing where both column are not empty.

But there are no ways of constructing a piece of VBA code to build such a query?

[MMcCarthy]

Hi Mary,

You were right, it returns everthing where both column are not empty.

But there are no ways of constructing a piece of VBA code to build such a query?

Remaniak

What exactly do you want your query to return and where do you want the returned records displayed.

Mary

[Remaniak]

Remaniak

What exactly do you want your query to return and where do you want the returned records displayed.

Mary

I want the query to return my table with only the columns that have values in them. (some have a header but no values)
From that query I will then make an export.

[MMcCarthy]

I want the query to return my table with only the columns that have values in them. (some have a header but no values)
From that query I will then make an export.

Even if you built that kind of code which would be very complicated you would then have a report based on the results. For example there may be 5 columns. What happens if the next time there are 6 columns. Your report only has 5. Doing what you are suggesting is extremely complicated and in all honesty I'm not sure how it would work.

If you change the AND in the select statement I gave you previously to an OR it will at least only return those records where at least one of the columns has a value.

Mary