469,299 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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

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
Dec 6 '06 #1
8 3711
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [ID], [Attribute 1]
  2. FROM [Data table]
  3. WHERE [ID] Is Not Null
  4. AND [Attribute 1] Is Not Null;
  5.  
Dec 6 '06 #2
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [ID], [Attribute 1]
  2. FROM [Data table]
  3. WHERE [ID] Is Not Null
  4. AND [Attribute 1] Is Not Null;
  5.  
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?
Dec 6 '06 #3
NeoPa
32,173 Expert Mod 16PB
No I'm afraid not.
There is no facility in SQL to determine columns dynamically.
Dec 6 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 7 '06 #5
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?
Dec 7 '06 #6
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 7 '06 #7
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.
Dec 7 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
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
Dec 7 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.