Results 1 to 6 of 6
  1. #1
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16

    Count in Multiple Fields

    Is there a way to get a short text count over multiple columns?

    For example: In a database for fruit sales, customers bought the following:



    1. Apples
    2. Apples & Grapes
    3. Bananas, Grapes, and Apples
    4. Grapes, Apples, and Strawberries

    So in this example, Apples can appear in any one of three columns. I can do counts for each column column for individual fruits, such as Apples.

    But is there a way to get a single count for Apples in all three columns?

    And if so, can I repeat the process for the other types of fruit in the same query? Or would I need to do a separate query for each fruit?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    aside from your tables would appear to not be set up correctly (should be one record per item so 3 items would be 3 records), only way I can think that might work is to combine the 3 columns which would be something like (assuming this is a query)

    numApples:sum(abs("Apples" IN (col1, col2,col3)))

  3. #3
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16
    Quote Originally Posted by CJ_London View Post
    aside from your tables would appear to not be set up correctly (should be one record per item so 3 items would be 3 records), only way I can think that might work is to combine the 3 columns which would be something like (assuming this is a query)

    numApples:sum(abs("Apples" IN (col1, col2,col3)))
    Thanks for your help. That did exactly what I needed.

    To clarify, they are individual records. The numbers 1 - 4 represent individual customers. So "customer 1" bought only apples, ""customer 2" bought apples and grapes, etc.

    Thanks again for your help.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    To clarify, they are individual records
    no - that's not what I mean - table should be structured

    customer....item
    1...............Apples
    2...............Apples
    2...............Grapes
    3...............Bananas
    3...............Grapes
    3...............Apples
    etc

    You have an Excel design in your head, not a database one.

    you have a workaround at the moment, but go much further and your workarounds will get more complex or simply not possible

    How many columns do you have? say 10. What if the next customer buys 11 items. Per above, no changes required. With your design, you need to add another column to the table, modify the query to include the extra column plus all other queries that you may have which might be affected. Then you need to change the forms and reports you are using. And what if you wanted to know the number of apples or pounds of grapes sold? What if there is a typo and you have Aple rather than Apple?

    At its simplest you should have a table for Customers, Items, a header table for the sale and a line item table for each line e.g.

    tblCustomers
    CustomerPK
    CustomerName

    tblItems
    ItemPK
    ItemName

    tblSales
    SalePK
    CustomerFK
    SaleDate

    tblSaleItems
    SaleItemPK
    SaleFK
    ItemFK
    Quantity

    PK=Primary Key - usually an autonumber which uniquely identifies a record
    FK=Foreign Key - usually a number (long) which links back to the relevant PK

    You might want other tables to store the price of items and another the cost. You might want a table for purchases which when you deduct the sales tells you how much you have in stock. I could go on but hopefully you can see your design is leading you down a path which will eventually dead end

  5. #5
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16
    Quote Originally Posted by CJ_London View Post
    You might want other tables to store the price of items and another the cost. You might want a table for purchases which when you deduct the sales tells you how much you have in stock. I could go on but hopefully you can see your design is leading you down a path which will eventually dead end
    Thanks again for you help. This process works for the one table. What I'm actually tracking are training issues. The higher ups want to categorize the issues on a primary, secondary, and (occasionally) tertiary basis.

    For example: Issue one is mainly an accuracy issue, but is also a professionalism issue (accuracy primary, professionalism secondary). Issue 2 may be mainly system use, but also accuracy (system use primary, accuracy secondary).

    I placed them in separate fields to avoid the many combinations of categories. It seemed cleaner to have only one category in each field. I also have the categories in combo boxes to avoid alternate spelling.

    So because "accuracy" can appear in any one of three fields, your formula to count in all three fields works great.

    You are correct in that this has limitations. I tried to apply a similar principle to the Training courses. We have a list of courses which employees must take, but not all employees take the same courses or in the same order. originally, I listed all possible course on the employees' records with a dedicated date field for each. But this leads to an unnecessarily cluttered record as most employees will have at least half the courses with no date (because they will never take those courses).

    So I made the course fields dropdowns to make them interchangeable.

    However, as you can imagine, that is proving problematic. Because the date fields are independent from the course fields, it is difficult to generate a report showing who has taken (for example) Onboarding and when (when being the tricky part). I can accomplish this on a limited scale through the Switch expression, but can only search a handful of fields that way.

    I feel, as you suggested , I'm going down the wrong path. If I go back to them being static fields, I'm looking at every employee record having some 20 + courses listed on their records of which most employees will have taken 6 or 7 in their careers.

    Is there a cleaner way to do this?

    Thanks again for your help.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I feel, as you suggested , I'm going down the wrong path. If I go back to them being static fields, I'm looking at every employee record having some 20 + courses listed on their records of which most employees will have taken 6 or 7 in their careers.
    the method I suggested
    tblemployees
    tblcourses
    linking tblempcourses containg employee fk, course fk and a date

    then you will have only 6 or 7 of these records for each employee

    for the future, better to be clear about what you actually need - disguising it is likely to result in a) wrong or incomplete advice which wastes everyone’s time b) rarely covers all aspects of the problem and c) can lead responders to suggesting you are ‘doing it wrong’ because the context is wrong

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

Similar Threads

  1. Count where multiple fields are the same
    By Rriemer in forum Access
    Replies: 4
    Last Post: 06-21-2017, 01:31 PM
  2. Count data from multiple fields
    By tbalci in forum Queries
    Replies: 5
    Last Post: 10-24-2014, 07:47 AM
  3. Replies: 6
    Last Post: 02-13-2014, 11:10 PM
  4. Replies: 1
    Last Post: 08-26-2013, 05:08 PM
  5. Multiple count of query fields
    By BLD21 in forum Queries
    Replies: 2
    Last Post: 05-23-2011, 01:09 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