Results 1 to 5 of 5

Creating Pivot Chart Out Of Multivalued Field.

  1. #1
    TokenMachine is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    9

    Creating Pivot Chart Out Of Multivalued Field.

    From what I've looked up, it seems impossible to make a Pivot chart of a multivalued field.

    What my employer wants is a chart based on Incident Injuries and their situations. There are 5 multivalued fields I need charts for.

    The Fields:


    Part Of Body
    Nature Of Injury
    Tools, Equipment, Or Work Area Contributions To Incident
    What Caused Or Influenced Substandard Conditions
    What Action Or Inaction Contributed To Incident


    These each have quite a few choices under them, and multiple can be selected. I was thinking I could get away with 5 different charts that I could show based on these fields I set up:


    Click image for larger version. 

Name:	1222.PNG 
Views:	11 
Size:	5.5 KB 
ID:	26973
    You choose one of the 5 sections, then second field appears and you choose one of the choices. There is a hidden Choice field that saves which choice you select when you hit Update Chart.

    I thought I could do a query that includes the 5 sections and their choices from my main records table, as well as the hidden Choice field's value to use as a filter.

    But after trying for a while it just doesn't seem possible at all. Is there a way I can achieve this they way I'm doing it, or is there an alternative?
    For clarification, I want to be able to use the Choice field to check if my multivalued field contains the string. And then count those on a chart.

  2. #2
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    1,749
    I suppose you've already been told/read that MVF's are evil? This is a post that might not get a lot of support because
    a) nobody here who's fairly knowledgeable would use them thus the chances of knowing how to solve the problem is slim
    b) anyone who would use them doesn't know enough to solve your problem
    So, I can only offer a shot in the dark because I've posted on how to get values from queries that act on table MVF's, but never for charting.
    If you can get the values listed out in a query by using the .value property of a mvf, then you might succeed. So if the query field is Customer (which holds the mvf value) then make it Customer.Value, or if it's for criteria, Customer.Value = "Acme"
    Last edited by Micron; 01-10-2017 at 06:17 PM. Reason: clarification
    - "doesn't work" doesn't help. Post error #s & text, if known.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects.

  3. #3
    TokenMachine is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    9
    Hmm, so could I use a query to pull values from a MVF, or does it handle the record's value as one string? If the latter, is there a way to check if the value contains a string? If it returns, "Improper Lifting, Lack Of Communication, OTHER", how could it tell if a record Contains [Value Box]'s "Improper Lifting"? Would the criteria be
    Code:
    Like "*" [Value Box].Value "*"

  4. #4
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    1,749
    The last two sentences of my post do answer those questions. Creating a test query would have given you a visual for your first question. Try that (you need to experiment when suggested so that you can learn) . You'll discover the answer to the second one too.

  5. #5
    TokenMachine is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    9
    Ok, so I accomplished this by separating each MVF into it's own query and chart. In the query under the MVF's .Value Field, I used my hidden [Value Box] on my Chart form as the criteria.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	3 
Size:	20.1 KB 
ID:	27006

    As I did with the Choice Field on the Chart form, I made the charts appear based on which Section is selected. Seems to work perfectly.

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

Similar Threads

  1. Issue creating a Pivot Chart
    By jefflab1 in forum Queries
    Replies: 1
    Last Post: 11-20-2014, 11:20 PM
  2. Replies: 0
    Last Post: 09-22-2014, 06:07 AM
  3. Creating a pivot chart in Excel
    By crowegreg in forum Programming
    Replies: 13
    Last Post: 02-19-2014, 08:42 AM
  4. Replies: 2
    Last Post: 07-12-2010, 04:39 AM
  5. Creating a Pivot Chart....
    By spcalan in forum Access
    Replies: 0
    Last Post: 01-08-2009, 02:28 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
  •  
Tech Forums: Microsoft Office Forums