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...

CodePudding user response：

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.

CodePudding user response：

You should normalize your schema. This one has the problem that it requires you to alter the table whenever you want to add or remove a genre.

You must have three tables:

1. Table Respondent: Respondent_ID (PK), Name, etc.
2. Table Genre: Genre_ID (PK), Name
3. Table Respondent_Genre: Respondent_ID (PK, FK), Genre_ID (PK, FK), Frequency

This also easily allows you to alter the name of a genre or to add additional attributes to a genre like genre and sub-genre or an annotation like (1930–present).

You could also have a lookup table for frequencies and then include the Frequency_ID in Respondent_Genre instead the Frequency as text.

Then you can write a query like this

SELECT r.Name, g.Name, rg.Frequency
FROM
    (Respondent r
     INNER JOIN Respondent_Genre rg
        ON r.Respondent_ID = rg.Respondent_ID)
    INNER JOIN Genre g
        ON rg.Genre_ID = g.Genre_ID
WHERE
    rg.Frequency = 'Very Frequently'