Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2015
    Posts
    2

    Unanswered: 2013 MS Access Multivalue Field Queries

    I have searched for the answer to this question on the internet and have not found one that fits exactly what I am trying to do so I do not know if it is even possible. But I have a table that tracks incidents for work with a multi-value field called "Event Type." I want a query to pull data for the previous 12 months and to total each value selected from the multi-value field. I have created a query and pulled the "Date" field and the "Event Type" field to the query column window. I have the criteria that will select the previous 12 months from the Date column so that the query will use only that data. But I do not know what criteria to enter in order to total each event type selected in the "Event Type" field so that I can have a count of the 9 different event types that occurred over the past 12 months. Does anyone have any suggestions on how I can accomplish this? I would appreciate any help you can provide.

    Brian

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use a query, the SQL will be something like:-
    Code:
    SELECT event_type, count (event_type) FROM my_table
    GROUP BY even_type
    I dont use the query designer so switch to SQL view inthe query designer
    Replave the lower case words above with the name of your column and table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2015
    Posts
    2

    Query suggestion

    Thanks so much! I will try entering that in SQL and see if it works. Do I need to enter language to sum each individual value type or will that language do it?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    each value type....
    assuming you've used proper relational design techniques then no for each different value in event_type you will get a count

    however if you have violated normalisation and got a pants physical design then who knows
    https://support.office.com/en-us/art...c-6de9bebbec31
    The idea behind multivalued fields is to make it easy to support those instances where you want to select and store more than one choice, without having to create a more advanced database design.
    multivalues fields have a role in Access design but not if you easily need to strip out the component parts.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •