Results 1 to 8 of 8
  1. #1
    rrobinson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    10

    Query to Return Total Count of Occurrences in 5 columns when a Condition is met

    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
    ID Flavor
    1 Vanilla
    2 Strawberry
    3 Chocolate
    4 Pistachio
    5 Rocky Road
    6 Butter Pecan
    Here’s what Orders table looks like
    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’d like the query to return
    Flavor Count
    Strawberry 4
    Chocolate 4
    Vanilla 3
    Rocky Road 2
    Butter Pecan 2
    Pistachio 1
    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).

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Table Orders is not a normalized data structure and this will complicate your efforts to do aggregate calcs. Really should be two related tables: Orders, OrderDetails.

    To accomplish without redesigning tables will first require a UNION query that rearranges the fields into a normalized structure which essentially emulates the table structure as it should be to begin with. Then use that query in subsequent queries, such as a GROUP BY or CROSSTAB, or as report RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rrobinson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    10
    Sorry, not sure what you mean "it's not normalized".

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do some research on the topic "database normalization", lots of tutorials, even Wiki has definition.

    Why do records 4 and 7 not have customer?

    Normalized data would be like:
    DetailID OrderID OrderDate Cust Flavor
    1 1 1/8/2015 Jane Chocolate
    2 1 1/8/2015 Jane Strawberry
    3 2 2/17/2015 Scott Vanilla
    4 2 2/17/2015 Scott Rocky Road
    5 2 2/17/2015 Scott Putter Pecan
    6 3 2/25/2015 Sally Chocolate

    That actually represents the result of joining Order, OrderDetails, Customers, Products tables in one query of a normalized data structure using autonumber fields as PK/FK for linking.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    rrobinson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    10
    OK, thanks for the clarification and suggestions. I'll look into them.

    As for the 2 records that don't have Customer names, I was trying to provide an example of a condition that would need to be met in order for that record to be counted. I'm actually working with tracking cases that go through an audit and if that case has error that are counted against a certain team, then i want those records to be counted and if it doesn't then I want it to be skipped. That was the best generic scenario I could think of that would resemble what I'm truly working on.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So these examples do not reflect your actual data - nothing to do with customers and ice cream? And there could be more than 6 'flavors'?

    Redesign or the UNION query are the best options I can see. Anything else would require VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rrobinson is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    10
    Thanks June7, I'm looking into both right now to see what the best/quickest possible solution would be.

    You're correct, there can actually be 90 different "flavors" that could be selected in these 5 columns and these 5 columns aren't always populated.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In the short term, UNION probably quickest. Best would be proper design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-07-2015, 10:50 AM
  2. Replies: 3
    Last Post: 06-02-2014, 10:41 PM
  3. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  4. Replies: 5
    Last Post: 03-17-2014, 04:02 PM
  5. Query to find condition and return field name
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 01-15-2014, 02:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums