I’m trying to create a query that returns the total number of occurrences a particular record has been selected in 5 columns when another column has data populated in it. For example, I have a list of ice cream flavors stored in table “Flavor Names” and these flavors can be selected in 5 different columns in table “Orders”. I want the query to return the number of times a flavor has been selected when a Customer is identified.
Here’s what Flavor Names table looks like
Here’s what Orders table looks like
ID Flavor 1 Vanilla 2 Strawberry 3 Chocolate 4 Pistachio 5 Rocky Road 6 Butter Pecan
Here’s what I’d like the query to return
ID Customer 1st Flavor 2nd Flavor 3rd Flavor 4th Flavor 5th Flavor 1 Jane Chocolate Strawberry 2 Scott Vanilla Rocky Road Butter Pecan 3 Sally Chocolate 4 Pistachio Rocky Road Butter Pecan Strawberry Vanilla 5 John Vanilla Pistachio Strawberry Chocolate Rocky Road 6 Patricia Vanilla Strawberry 7 Chocolate Pistachio Vanilla 8 James Butter Pecan Strawberry Chocolate
Here’s what I have tried and it will only return the occurrences in the 1st column and some flavors appear twice with different numbers (combining the numbers gives the correct count in the 1st column).
Flavor Count Strawberry 4 Chocolate 4 Vanilla 3 Rocky Road 2 Butter Pecan 2 Pistachio 1
SELECT Orders.[1st Flavor] AS [Flavor], Count([1st Flavor] & [2nd Flavor] & [3rd Flavor] & [4th Flavor] & [5th Flavor]) AS [Total Count]
FROM Flavor Names RIGHT JOIN Orders ON (Flavor Names.[Flavor] = Orders.[3rd Flavor]) AND (Flavor Names.[Flavor] = Orders.[2nd Flavor]) AND (Flavor Names.[Flavor] = Orders.[1st Flavor]) AND (Flavor Names.[Flavor] = Orders.[5th Flavor]) AND (Flavor Names.[Flavor] = Orders.[4th Flavor])
WHERE (((Orders.[Customer]) Is Not Null))
GROUP BY Orders.[1st Flavor]
ORDER BY Count([1st Flavor] & [2nd Flavor] & [3rd Flavor] & [4th Flavor] & [5th Flavor]) DESC;
Any assistance in correcting my query or suggestions will be greatly appreciated.
Thank you!