Function to show the fields with Yes value in a table query

[dhen21dx]

Asking your idea how i can identify fields that has Yes value.
Thanks

 

[Joe4]

Do you mean that you want to return records where some field is equal to "Yes"?
Just make sure that field is in your query, and put the word "Yes" on the Critieria line of that field (in Query Builder).

If that is not what you mean, please post a small sampling of data along with your expected results.

 

[dhen21dx]

Hello,

No, i want to return the field name header if record on columns has a yes..

 

[Joe4]

I don't think that can be done with a query. That would probably be more of a VBA kind of thing.

I would be interested to see how your data tables are designed. You may have a normalization issue going on.

 

[Micron]

Agree, but not enough info. I can imagine at least 4 possible meanings to that.
One table, one field to check? One table many fields to check?
Many tables, ...

 

[dhen21dx]

Yes, sorry about the missing information, actually multiple fields to check in a table(query). If the field has Yes, then i need to get the header name. i will use this to inform the user of what fields in a table that has Yes for their correction.

 

[Micron]

IMO, only code can do this. If you can make use of a procedure, I'll see what I can come up with or you can try it yourself if you're game. Off the top of my head, basically, you'd have to:
- create a recordset based on your table or query. The name of that would be a help.
- starting at the first record (this is an outer loop), and in a loop within that loop (inner loop) loop over the fields looking for Yes
- if you find it, add the field name to a variable
- when the inner loop is finished, exit and then MoveNext in the recordset.
- rinse and repeat until no more records
- do something with the variable value that results - message box? Or add the field names to another table? Whatever that is, it could add a fair amount of code if it's complex.

That is the condensed and simple explanation. Other things would be helpful, such as ensuring the recordset has values and trapping for errors.
Regardless, how much help is it to know that there is a yes in a field when you don't know which of the 7,000 records it is in?

Is that Yes text or is it a Y/N field - aka Boolean, True/False, -1/0 ??

P.S. - I agree, sounds like a db normalization problem but since you didn't respond to that comment I gather that's not going to change.

 

[james_lankford]

create a table named
a_blank_template_table

this table has 3 fields
id = autonumber, primary key
field_name = Short text
number_of_yes = Number


now take this querybelow and change
field_name_1 to be the name of your field that might contain a Yes
field_name_2 to be the name of your field that might contain a Yes
field_name_3 to be the name of your field that might contain a Yes

note: keep the single quotes in the select statement
so if your field name was product_has_been_recalled
you would have
select 'product_has_been_recalled' as [field_name], count(*) as [number_of_yes] from table_name where product_has_been_recalled = Yes group by product_has_been_recalled

insert into a_blank_template_table
(
field_name,
number_of_yes
)
select * from
(
select 'field_name_1' as [field_name], count(*) as [number_of_yes] from table_name where field_name_1 = Yes group by field_name_1
union all
select 'field_name_2' as [field_name], count(*) as [number_of_yes] from table_name where field_name_2 = Yes group by field_name_2
union all
select 'field_name_3' as [field_name], count(*) as [number_of_yes] from table_name where field_name_3 = Yes group by field_name_3
)

after you've changed the above query run it
if product_has_been_recalled has a Yes in it then
a_blank_template_table
will contain the text
product_has_been_recalled
and
how many Yes's were found

but if it contians zero Yes's
then the table will not have an entry for that field

 

[Micron]

You would do this if there were say, 20 or more fields? I guess I shouldn't have said code was the only way when what I really meant is that it's the most practical. Best part might be that it would work without editing regardless of how many fields there might be. There are limits to Union queries (255 total fields and 64Kb?) but so far, no way to know if that would approach any limit. That still doesn't tell you what record contains the yes? Could be thousands of records?

 

[dhen21dx]

You would do this if there were say, 20 or more fields? I guess I shouldn't have said code was the only way when what I really meant is that it's the most practical. Best part might be that it would work without editing regardless of how many fields there might be. There are limits to Union queries (255 total fields and 64Kb?) but so far, no way to know if that would approach any limit. That still doesn't tell you what record contains the yes? Could be thousands of records?

Hi Micron,

Yes, i have like 30 field which is dynamic. so i need VBA is the best solution. By the way i will add the result in a table (list of field that have Yes)