Return only "Yes" values - multiple fields

[Guest]

I have a "USER" table related one-to-many to seven other tables, one for each
system the user has access to. Each of those with Yes/No fields for every
aspect of the system the user has access to.

I want to build a query that returns only those fields with a "Yes" answer
from all seven tables. Since all together there could be approximately 70
fields, how can I do this? Thank you.

 

[Michel Walsh]

Hi,


The field name is not part of the (accessible) data, at least, not easily,
not in SQL. You have to change the design of the table to make it "as data":

you can use:


SELECT User, "System1" As System, System1 As Access FROM myTable
UNION ALL
SELECT User, "System2", System2 FROM myTable
UNION ALL
....
SELECT User, "System70", System70 FROM myTable



and then use that query, a little bit like

SELECT * FROM myUnionAllQuery WHERE Access



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks V....I will work with your suggestion, I appreciate your help
--
Jeff C
Live Well .. Be Happy In All You Do

Hi,


The field name is not part of the (accessible) data, at least, not easily,
not in SQL. You have to change the design of the table to make it "as data":

you can use:


SELECT User, "System1" As System, System1 As Access FROM myTable
UNION ALL
SELECT User, "System2", System2 FROM myTable
UNION ALL
....
SELECT User, "System70", System70 FROM myTable



and then use that query, a little bit like

SELECT * FROM myUnionAllQuery WHERE Access



Hoping it may help,
Vanderghast, Access MVP