Search code examples
sqlms-access

How to design a query in WHERE clause of all column that contain same data value?

I have a table, the columns are:

Respondent_ID, classical, gospel, pop, kpop, country, folk, rock, metal ... (all genre of music)

there are 16 columns of different type of genre of music, and data value is Never, Rarely, Sometimes or Very frequently

SELECT *
FROM genre_frequency
WHERE

I want to design a query which show results of all columns in the table what has the value 'Very Frequently', can anyone lend me a hand here? I'm still new to this, please help anyone...

Solution

  • Could put the same criteria under every genre field with OR operator - very messy. Or could use a VBA custom function.

    Or could normalize data structure so you have fields: RespondentID, Genre, Frequency. A UNION query can rearrange data to this normalized structure (unpivot). There is a limit of 50 SELECT lines and there is no builder or wizard for UNION - must type or copy/paste in SQL View.

    SELECT Respondent_ID, "classical" AS Genre, classical AS Frequency FROM genre_frequency
UNION SELECT Respondent_ID, "gospel", gospel FROM genre_frequency
... {continue for additional genre columns};

    Now use that query like a table in subsequent queries. Just cannot edit data.
    SELECT * FROM qryUNION WHERE Frequency="Very frequently";

    UNION query can perform slowly with very large dataset. Probably would be best to redesign table. Could save this rearranged data to a table. If you want to utilize lookup tables for Genre and Frequency in order to save ID keys instead of full descriptive text, that can also be accommodated in redesign.