Query for multi-value combo-box (1 Viewer)

falcondeer

Registered User.
Local time
Today, 14:49
Joined
May 12, 2013
Messages
101
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.
 

Attachments

  • Pic1.png
    Pic1.png
    19.9 KB · Views: 153
  • pic2.png
    pic2.png
    14 KB · Views: 185
  • pic3.png
    pic3.png
    17.9 KB · Views: 138
  • pic4.png
    pic4.png
    10.5 KB · Views: 180

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:49
Joined
May 7, 2009
Messages
19,169
try something like this:
 

Attachments

  • total_query.png
    total_query.png
    11.7 KB · Views: 195

June7

AWF VIP
Local time
Today, 13:49
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 14:49
Joined
May 12, 2013
Messages
101
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
 

Attachments

  • Pic5.png
    Pic5.png
    17.1 KB · Views: 150

June7

AWF VIP
Local time
Today, 13:49
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom