Query for multi-value combo-box

[falcondeer]

Hi

I have main form that got many fields like patient name, gender...etc and one multi-value combo-box for associated diseases as in (pic1).

the combo-box is working fine (Pic1) and I needed to calculate number of associated diseases for gender(M,F...etc) so I created a query (pic2 and pic3) and a pivot-chart to do so.

The problem is the following:

The pivot chart (pic4) does not show the real number of associated diseased entered in the combo-boxes, I guess it counts only the first value in each comb box.

How can i fix that.

Thanks.

 

[June7]

Your query is not correct. Review https://support.office.com/en-us/ar...ries-6f64f92d-659f-411c-9503-b6624e1e323a#bm3

Advise NOT to use multi-value fields.

 

[isladogs]

Agree with June's advice not to use multivalue fields as they will cause you major issues managing your data.
See Multivalue fields ... and why you really shouldn't use them

 

[arnelgp]

try something like this:

 

[falcondeer]

Agree with June's advice not to use multivalue fields as they will cause you major issues managing your data.
See Multivalue fields ... and why you really shouldn't use them

Thanks guys for the replies,

I agree with you and with June7, but what can I do if the user could choose big number of choices in one field.

 

[June7]

Need a related dependent table to save a record for each disease. How those records are created can be done by normal old data entry one record at a time or select items from multi-select listbox and code writes a record for each selected item.

Or continue with multi-value field and deal with its issues. If fully understood, can make work.

 

[falcondeer]

try something like this:

When I run the query It will give me the numbers like in (pic5) but when I try to make a pivot-chart out of it It wont, I don't know why.

Thanks

 

[June7]

I've never used pivot charts/tables/forms. I have built graphs and crosstab queries. I have never used multi-value fields. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[isladogs]

In my website article about MVFs, I said don't even think about using them with crosstab queries!
The same applies to pivot tables/charts (which were deprecated after A2010)
You might be able to do it based on your MVF aggregate query if you use a make table query based on it.
However I would strongly recommend you convert the MVF field back to text and have several records instead of one where multiple items are chosen