Search code examples
vbams-access-2010pivot-chart

VBA Pivot Chart Categories Not Counting or Displaying Correctly

I have a table that uses another table's values as a look up for fields in a data table.

[Parameters] is the table that holds the look up values and

[Sample Log] is the data table that looks up possible values from [Parameters].

[Sample Log].exp = SELECT Parameters.Experiment FROM [Parameters]; 
[Sample Log].Type = SELECT Parameters.Type FROM [Parameters];

If I create a Pivot Chart using [Sample Log] as the source, and put Type in the categories with my Count of IDs as the data, it will behave as expected.

However, with my exp field, it seems to not match thefull text, but only the first two letters of the text. My experiments for instance are GC, GPC, GCMS, UV-Vis, HPLC, etc... but the category lables only show GC, GP, UV, HP, etc... AND entries in the data table with GCMS are counted as GC on the pivot chart.

This figure pretty much sums it up where if I have something as GCMS, it gets counted as GC. I verified by changing GCMS to GC, the count of IDs value remained at 36, however, if I change it to HPLC, the HP count increments and GC count goes down. So GCMS was counted in GC.

Any insight how to control this counting behavior?

This problem only manifests when turning on allowing of multiple selections in the combo box. It creates a second exp.Value field in my table, but this doesn't list all of the values...

enter image description here

Solution

  • The desired results can be obtained by modifying my query statement to rename the multivalued .Value field. For example, my original query statement was:

    SELECT [Sample Log].ID, [Sample Log].material, [Sample Log].lot, [Sample Log].subDate, [Sample Log].endDate, [Sample Log].Exp.Value, [Sample Log].costCenter, [Sample Log].customer, [Sample Log].Type
FROM [Sample Log];

    The working statment is:

    SELECT [Sample Log].ID, [Sample Log].material, [Sample Log].lot, [Sample Log].subDate, [Sample Log].endDate, [Sample Log].Exp.Value AS Expirment, [Sample Log].costCenter, [Sample Log].customer, [Sample Log].Type
FROM [Sample Log];

    [Sample Log].Exp.Value AS Expirment