By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

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

P: 16
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
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 16
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
Expert Mod 15k+
P: 31,660
No I'm afraid not.
There is no facility in SQL to determine columns dynamically.
Dec 6 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 16
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
Expert Mod 10K+
P: 14,534
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

P: 16
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
Expert Mod 10K+
P: 14,534
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.