none
How to update linked box in Visio when the source disappears RRS feed

  • Question

  • I have a Visio diagram where some boxes are linked to access database queries. These queries count certain values by category, for example:

    High: 2

    Medium: 3

    Low: 1

    and so on, however, if any of the categories does not have value anymore, the query does not return it but that does not reflect in Visio. I would like to still see the category in visio but the value would be Zero, is that possible?

    Sunday, August 30, 2015 3:56 PM

Answers

  • SELECT tblRecruiting.RefusalCategory, Count(tblRecruiting.MRN) AS Total
    FROM tblRecruiting
    GROUP BY tblRecruiting.RefusalCategory
    HAVING (((tblRecruiting.RefusalCategory) In ('CG Distressed','CG Not Interested','CG Not Physically Well','CG Other - See Comments','CG Too Busy/Too Much Going On','CG Too Old')));
    • Marked as answer by al.sallam Monday, September 7, 2015 3:21 PM
    Sunday, August 30, 2015 7:50 PM
  • Create a table tblRefusalCategory with field RefusalCategory listing all categories.   Use this query --

    SELECT tblRefusalCategory.RefusalCategory, Count(tblRecruiting.MRN) AS Total
    FROM tblRecruiting RIGHT JOIN tblRefusalCategory ON tblRecruiting.RefusalCategory = tblRefusalCategory.RefusalCategory
    WHERE (((tblRecruiting.RefusalCategory) In ('CG Distressed','CG Not Interested','CG Not Physically Well','CG Other - See Comments','CG Too Busy/Too Much Going On','CG Too Old') Or (tblRecruiting.RefusalCategory) Is Null))
    GROUP BY tblRefusalCategory.RefusalCategory;


    Build a little, test a little

    Monday, August 31, 2015 2:16 AM

All replies

  • Use Nz function   OR    IIF([XXXX] Is Null, 0, [XXXX])   in the query.

    If you need more help then post the SQL of the query.


    Build a little, test a little

    Sunday, August 30, 2015 4:03 PM
  • Yes, but if the category does not have value the category does not get returned in the query so, instead of having High, medium, low. if low does not have value, the result set will only return high and medium. I need all 3 values to be returned even if one does not exist
    Sunday, August 30, 2015 5:25 PM
  • Post the SQL of your query.

    Build a little, test a little


    Sunday, August 30, 2015 6:07 PM
  • SELECT tblRecruiting.RefusalCategory, Count(tblRecruiting.MRN) AS Total
    FROM tblRecruiting
    GROUP BY tblRecruiting.RefusalCategory
    HAVING (((tblRecruiting.RefusalCategory) In ('CG Distressed','CG Not Interested','CG Not Physically Well','CG Other - See Comments','CG Too Busy/Too Much Going On','CG Too Old')));
    • Marked as answer by al.sallam Monday, September 7, 2015 3:21 PM
    Sunday, August 30, 2015 7:50 PM
  • Create a table tblRefusalCategory with field RefusalCategory listing all categories.   Use this query --

    SELECT tblRefusalCategory.RefusalCategory, Count(tblRecruiting.MRN) AS Total
    FROM tblRecruiting RIGHT JOIN tblRefusalCategory ON tblRecruiting.RefusalCategory = tblRefusalCategory.RefusalCategory
    WHERE (((tblRecruiting.RefusalCategory) In ('CG Distressed','CG Not Interested','CG Not Physically Well','CG Other - See Comments','CG Too Busy/Too Much Going On','CG Too Old') Or (tblRecruiting.RefusalCategory) Is Null))
    GROUP BY tblRefusalCategory.RefusalCategory;


    Build a little, test a little

    Monday, August 31, 2015 2:16 AM