how to show only these columns?

[geoffcox]

Hello
I have a table with some 20 columns one of which can have the text
"yes" in it or is left blank.

I would like to have a query which only shows those columns with "yes"
in them.

At the moment I see all the columns, those with and those without the
"yes" ...

By the way I changed from a yes/no type as it was so easy to
accidentally change the value when looking at the entries.

Thanks

Geoff

 

[bob fitz]

Open your query in design view. Type "Yes" in the criteria line, for the column to which this criteria is to be applied.

 

[geoffcox]

Thanks for your reply.

I have tried entering "yes" in the criteria but have just realised the error in my thinking! Any column may have just one "yes" in it and all the other entries will be "no" - but that column will have to be there.

How can I generate queries for each individual record and then only show columns which have "yes" in them?

Cheers

Geoff

 

[bob fitz]

You will need to enter the "Yes" criteria in each column, but on a seperate line for each.

 

[geoffcox]

Bob

I seem to be missing the point!

The sql is below but this lists all the schools and I want to just give results for Greens School and not to show columns if the entry is not "yes".

??!

Cheers

Geoff

SELECT the_schools.school_name, the_2011.btec_intro, the_2011.btec_first_unit1, the_2011.btec_first_unit2, the_2011.btec_first_unit3
FROM the_2011
WHERE (((the_2011.school_name)="Greens School") AND ((the_2011.btec_intro)="yes")) OR (((the_2011.btec_first_unit1)="yes")) OR (((the_2011.btec_first_unit2)="yes")) OR (((the_2011.btec_first_unit3)="yes"));

 

[bob fitz]

First of all, the sql you posted seems to be wrong.
SELECT the_schools.school_name, the_2011.btec_intro, ............

the_schools should be the_2011 if the field <school_name> is in the same table as the other fields.

You will need to apply the school name to every criteria line. Something like:
SELECT the_2011.school_name, the_2011.btec_intro, the_2011.btec_first_unit1, the_2011.btec_first_unit2, the_2011.btec_first_unit3
FROM the_2011
WHERE (((the_2011.school_name)="Greens School") AND ((the_2011.btec_intro)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit1)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit2)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit3)="yes"));

Try to copy and paste the sql above into a new query and let us know if this works for you.

 

[geoffcox]

Bob

apologies for the name error.

Your sql works except that it shows "no" in the btec_intro column and I am hoping to be able to not show those columns with "no" in them.

?

Cheers

Geoff

SELECT the_2011.school_name, the_2011.btec_intro, the_2011.btec_first_unit1, the_2011.btec_first_unit2, the_2011.btec_first_unit3
FROM the_2011
WHERE (((the_2011.school_name)="Greens School") AND ((the_2011.btec_intro)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit1)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit2)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit3)="yes"));

 

[bob fitz]

Hi Geoff

I'm not sure that I understand your requirments, but give this a try and post back:

SELECT the_2011.school_name, IIf([btec_intro]="no","",[btec_intro]) AS intro, the_2011.btec_first_unit1, the_2011.btec_first_unit2, the_2011.btec_first_unit3
FROM the_2011
WHERE (((the_2011.school_name)="Greens School") AND ((IIf([btec_intro]="no","",[btec_intro]))="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit1)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit2)="yes")) OR (((the_2011.school_name)="Greens School") AND ((the_2011.btec_first_unit3)="yes"));

 

[geoffcox]

Bob

Thanks for your code. This still gives a column in which the value is not "yes".

I had posted in comp.databases.ms-access and learnt that a query cannot "decide" to leave out a column but the sql below does give me the answer by listing the columns which have a "yes" in one column and the school name in the other.

Cheers

Geoff

SELECT the_2011.school_name, IIf(btec_intro="yes","btec_intro ") &
IIf(btec_first_unit1="yes","btec_first_unit1 ") &
IIf(btec_first_unit2="yes","btec_first_unit2 ") &
IIf(btec_first_unit3="yes","btec_first_unit3 ") AS Expr1
FROM the_2011
WHERE (((the_2011.[school_name])="Greens School"));