Graphing an "Other" section in a Pie Graph

[pushrodengine via AccessMonster.com]

I created a pie graph that contains information from a query in Access 2003.
The graph shows the percentage of the occurrences of items in the query.

The problem I’m having is the graph becomes extremely cluttered when there
are many items in the graph. I would like to streamline my graph to only
include items that occur most often. For all the other items I would like to
insert those in to their own piece of the pie label “Otherâ€, so that the
percentage still mistakes sense.

How can I make an “Other†section of the pie?

Thank You

 

[Duane Hookom]

You would need to figure out how to create the Other section in a query. I
expect you could use a query with a subquery or something similar. You would
have to define exactly what you would want to combine.

 

[pushrodengine via AccessMonster.com]

Thank you for relying.


I know exactly what I need to combined.

How do I create a query with a subquery?



Thanks

 

[Duane Hookom]

Assuming a query qgrpItemCount that returns the Item and ItemCount, you could
create a query "qgrpItemOther" with a SQL like:

SELECT qgrpItemCount.ItemCount, IIf([ItemCount]<=(SELECT Min(ItemCount) FROM
qgrpItemCount),"Other",[Item]) AS ItemOther
FROM qgrpItemCount;

Then create another query to combine the "other" like:
SELECT Sum(qgrpItemOther.ItemCount) AS SumOfItemCount, qgrpItemOther.ItemOther
FROM qgrpItemOther
GROUP BY qgrpItemOther.ItemOther;

 

[pushrodengine via AccessMonster.com]

Thank you I'll give it a shot.

Assuming a query qgrpItemCount that returns the Item and ItemCount, you could
create a query "qgrpItemOther" with a SQL like:

SELECT qgrpItemCount.ItemCount, IIf([ItemCount]<=(SELECT Min(ItemCount) FROM
qgrpItemCount),"Other",[Item]) AS ItemOther
FROM qgrpItemCount;

Then create another query to combine the "other" like:
SELECT Sum(qgrpItemOther.ItemCount) AS SumOfItemCount, qgrpItemOther.ItemOther
FROM qgrpItemOther
GROUP BY qgrpItemOther.ItemOther;

Thank you for relying.

[quoted text clipped - 6 lines]

 

[pushrodengine via AccessMonster.com]

SELECT qgrpItemCount.ItemCount, IIf([ItemCount]<=(SELECT Min(ItemCount) FROM

qgrpItemCount),"Other",[Item]) AS ItemOther
FROM qgrpItemCount;

Where do I place this information into the query?

Thanks

 

[Duane Hookom]

This was the SQL view of a query that I created in order to get some grouping
for "other". You didn't provide any SQL views or table and field names so I
created some of my own to do some testing.

--
Duane Hookom
Microsoft Access MVP

SELECT qgrpItemCount.ItemCount, IIf([ItemCount]<=(SELECT Min(ItemCount) FROM
qgrpItemCount),"Other",[Item]) AS ItemOther
FROM qgrpItemCount;

Where do I place this information into the query?

Thanks

 

[pushrodengine via AccessMonster.com]

You didn't provide any SQL views

Thank you for the clarification.

All try to provide more information:


“Count†– is labeled “qryGraphMedicalCountâ€

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail
FROM tblIncidentLog
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Alcohol Abuse" Or (tblIncidentLog.CallDetail)="Allergic Reaction" Or
(tblIncidentLog.CallDetail)="Anxiety Attack" Or (tblIncidentLog.CallDetail)
="Childbirth - Delivery at Scene" Or (tblIncidentLog.CallDetail)="Childbirth -
Delivery Enroute" Or (tblIncidentLog.CallDetail)="Childbirth - Prior
Delivery" Or (tblIncidentLog.CallDetail)="Choking"));

“Other†– is labeled “qryGraphMedicalOtherâ€

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail
FROM tblIncidentLog
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Diabetic Emergency" Or (tblIncidentLog.CallDetail)="Difficulty Swallowing"
Or (tblIncidentLog.CallDetail)="Leg Infection" Or (tblIncidentLog.CallDetail)
="Medical Alarm - UTL" Or (tblIncidentLog.CallDetail)="Medication Reaction"
Or (tblIncidentLog.CallDetail)="Nose Bleed - Controlled" Or (tblIncidentLog.
CallDetail)="Nose Bleed - Uncontrolled"));


I need to combine the “Count†with the “Otherâ€, but when the graph is created
I need the items in “qryGraphMedicalCount†to remain labeled as are, for
example “Childbirth - Delivery at Sceneâ€, “Childbirth - Delivery Enrouteâ€.

Within the same graph the items in “qryGraphMedicalOther†are labeled “Otherâ€,
for example “Diabetic Emergencyâ€, “Difficulty Swallowingâ€, “Leg Infection",
and “Medical Alarm – UTL†all appear in within the graph as “Otherâ€


Could you provide instructions on how to do this?

Thank you for all the time and help.
Happy Thanksgiving!

 

[Duane Hookom]

You seem to know which values belong in the "Other". You should have a table
of unique values of CallDetails. Add a yes/no field named "IsOther". Set the
value of this field to False for all records except those you want to be
included with "Other". Then in your query, add the table and GROUP BY the
expression:
CallDtl: IIf(IsOther, "Other", [CallDetail])

BTW: you shouldn't be hard-coding values in your query criteria. Assume you
will be adding more values for CallDetail. You shouldn't be forced to
maintain your expressions in your queries. Use data values to manage your
expressions.
--
Duane Hookom
Microsoft Access MVP

You didn't provide any SQL views

Thank you for the clarification.

All try to provide more information:


“Count†– is labeled “qryGraphMedicalCountâ€

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail
FROM tblIncidentLog
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Alcohol Abuse" Or (tblIncidentLog.CallDetail)="Allergic Reaction" Or
(tblIncidentLog.CallDetail)="Anxiety Attack" Or (tblIncidentLog.CallDetail)
="Childbirth - Delivery at Scene" Or (tblIncidentLog.CallDetail)="Childbirth -
Delivery Enroute" Or (tblIncidentLog.CallDetail)="Childbirth - Prior
Delivery" Or (tblIncidentLog.CallDetail)="Choking"));

“Other†– is labeled “qryGraphMedicalOtherâ€

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail
FROM tblIncidentLog
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Diabetic Emergency" Or (tblIncidentLog.CallDetail)="Difficulty Swallowing"
Or (tblIncidentLog.CallDetail)="Leg Infection" Or (tblIncidentLog.CallDetail)
="Medical Alarm - UTL" Or (tblIncidentLog.CallDetail)="Medication Reaction"
Or (tblIncidentLog.CallDetail)="Nose Bleed - Controlled" Or (tblIncidentLog.
CallDetail)="Nose Bleed - Uncontrolled"));


I need to combine the “Count†with the “Otherâ€, but when the graph is created
I need the items in “qryGraphMedicalCount†to remain labeled as are, for
example “Childbirth - Delivery at Sceneâ€, “Childbirth - Delivery Enrouteâ€.

Within the same graph the items in “qryGraphMedicalOther†are labeled “Otherâ€,
for example “Diabetic Emergencyâ€, “Difficulty Swallowingâ€, “Leg Infection",
and “Medical Alarm – UTL†all appear in within the graph as “Otherâ€


Could you provide instructions on how to do this?

Thank you for all the time and help.
Happy Thanksgiving!

 

[pushrodengine via AccessMonster.com]

You should have a table of unique values of CallDetails.
All of the information for both "other" and "non-other" come from the same
main table called “tblIncidentLogâ€.

Add a yes/no field named "IsOther".

This would require the user to make the judgment on whether entry is “otherâ€,
so I’d rather not do that.

 

[Duane Hookom]

"user to make the judgment on whether entry is “otherâ€".
Your previous reply in this string had a query defined as "other". How did
you go from defining other as :
==========================
(tblIncidentLog.CallDetail)="Diabetic Emergency" Or
(tblIncidentLog.CallDetail)="Difficulty Swallowing"
Or (tblIncidentLog.CallDetail)="Leg Infection" Or (tblIncidentLog.CallDetail)
="Medical Alarm - UTL" Or (tblIncidentLog.CallDetail)="Medication Reaction"
Or (tblIncidentLog.CallDetail)="Nose Bleed - Controlled" Or (tblIncidentLog.
CallDetail)="Nose Bleed - Uncontrolled"));
==========================
to not knowing what "other" is?

 

[pushrodengine via AccessMonster.com]

You should have a table of unique values of CallDetail.

My table is “tblIncidentLog†with a column called “CallDetailâ€. All of the
information that goes into “CallDetail†is all hand entered by a user.

From there I created a query called ““qryGraphMedicalâ€, this only isolates
the Medical “CallDetail†items. When I generate a graph based on all this
data there’s too many items. The graph is too over whelming, so I need to
group items that occur less frequently into an “other†section of the graph
to clean it up.

I was assuming the user would have to answer for “IsOtherâ€.

I know what CallDetails I would like in “other†section of the pie graph, but
don’t know hold to isolate them within the same query the graph is generated
from.

All I did was create a query that separated “other†(qryGraphMedicalOther),
nothing more.

I’m sorry for the misunderstanding.

 

[Duane Hookom]

You state "My table is “tblIncidentLog†with a column called “CallDetailâ€.
All of the
information that goes into “CallDetail†is all hand entered by a user."
Don't users select this from a lookup table of unique CallDetail values? If
you don't have one then create it and add the column that determines whether
or not the CallDetail values is aggregaged into the "Other" CallDetail.

 

[pushrodengine via AccessMonster.com]

Don't users select this from a lookup table of unique CallDetail values?

Yes you’re absolutely right, the user does select from a table called
“tblDetailsâ€.

add the column that determines whether or not the CallDetail values is aggregaged into >the "Other" CallDetail.

Ok, I’ve now created a new column the table “tblDetails†called “IsOtherâ€
with yes/no Data Type. I’ve check all the boxes belonging to CallDetail
values that I want set as “Otherâ€.

Where do I go from here?


I really appreciate your help and patience!!!

Thank you

 

[Duane Hookom]

From a previous post: Then in your query, add the table and GROUP BY the
expression:
CallDtl: IIf(IsOther, "Other", [CallDetail])

 

[pushrodengine via AccessMonster.com]

Then in your query, add the table and GROUP BY the

expression:
CallDtl: IIf(IsOther, "Other", [CallDetail])

I followed your instructions and I get an error that reads:

“The expression you entered has an invalid . (dot) or ! operator or invalid
parentheses. You may have entered an invalid identifier or typed parentheses
following the Null constantâ€

 

[Duane Hookom]

Please provide your actual SQL view. The expression looks fine.

--
Duane Hookom
Microsoft Access MVP

Then in your query, add the table and GROUP BY the
expression:
CallDtl: IIf(IsOther, "Other", [CallDetail])

I followed your instructions and I get an error that reads:

“The expression you entered has an invalid . (dot) or ! operator or invalid
parentheses. You may have entered an invalid identifier or typed parentheses
following the Null constantâ€

 

[pushrodengine via AccessMonster.com]

Please provide your actual SQL view. The expression looks fine.

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail, tblDetails.IsOther
FROM tblIncidentLog, tblDetails
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail, tblDetails.IsOther
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Alcohol Abuse" Or (tblIncidentLog.CallDetail)="Allergic Reaction" Or
(tblIncidentLog.CallDetail)="Anxiety Attack" Or (tblIncidentLog.CallDetail)
="Childbirth - Delivery at Scene" Or (tblIncidentLog.CallDetail)="Childbirth -
Delivery Enroute" Or (tblIncidentLog.CallDetail)="Childbirth - Prior
Delivery" Or (tblIncidentLog.CallDetail)="Choking" Or (tblIncidentLog.
CallDetail)="CPR - Continued Through Transport" Or (tblIncidentLog.CallDetail)
="CVA / TIA Signs and Symptoms" Or (tblIncidentLog.CallDetail)="Diabetic
Emergency" Or (tblIncidentLog.CallDetail)="Difficulty Swallowing" Or
(tblIncidentLog.CallDetail)="Leg Infection" Or (tblIncidentLog.CallDetail)
="Medical Alarm - UTL" Or (tblIncidentLog.CallDetail)="Medication Reaction"
Or (tblIncidentLog.CallDetail)="Nose Bleed - Controlled" Or (tblIncidentLog.
CallDetail)="Nose Bleed - Uncontrolled" Or (tblIncidentLog.CallDetail)="GI
Bleed" Or (tblIncidentLog.CallDetail)="Miscarriage / Vaginal Bleeding" Or
(tblIncidentLog.CallDetail)="Diarrhea - Extended" Or (tblIncidentLog.
CallDetail)="General Weakness" Or (tblIncidentLog.CallDetail)="Misc. Medical
Incident" Or (tblIncidentLog.CallDetail)="Shaking" Or (tblIncidentLog.
CallDetail)="Cardiac Chest Pain" Or (tblIncidentLog.CallDetail)="Cardiac
Dysrhythmia" Or (tblIncidentLog.CallDetail)="Hypertension" Or (tblIncidentLog.
CallDetail)="Hypotension" Or (tblIncidentLog.CallDetail)="Irregular Heart
Beat" Or (tblIncidentLog.CallDetail)="SOB - Asthma" Or (tblIncidentLog.
CallDetail)="SOB - CHF" Or (tblIncidentLog.CallDetail)="SOB - COPD" Or
(tblIncidentLog.CallDetail)="SOB - Hyperventilation" Or (tblIncidentLog.
CallDetail)="11-44 - Terminated Enroute" Or (tblIncidentLog.CallDetail)
="Abdominal Pain" Or (tblIncidentLog.CallDetail)="Psychiatric - 5150 by Law
Enforcement" Or (tblIncidentLog.CallDetail)="Seizure" Or (tblIncidentLog.
CallDetail)="11-44 - Non-Initiation" Or (tblIncidentLog.CallDetail)="11-44 -
Terminated At Scene" Or (tblIncidentLog.CallDetail)="SOB - Other" Or
(tblIncidentLog.CallDetail)="CVA / TIA Signs and Symptoms" Or (tblIncidentLog.
CallDetail)="Dizziness" Or (tblIncidentLog.CallDetail)="Syncope" Or
(tblIncidentLog.CallDetail)="Altered LOC") AND ((tblDetails.IsOther)=If
("IsOther","Other",[CallDetail])));

 

[Duane Hookom]

I don't think you understand how to create the query. You don't have a join
between the two tables. You should never have so many hard-coded values in
the criteria. That's why we store data in fields. Try something like:

SELECT CallDtl: IIf(IsOther, "Other", tblIncidentLog.[CallDetail]),
Count(tblIncidentLog.CallDetail) AS CountOfCallDetail,
FROM tblIncidentLog JOIN tblDetails ON tblIncidentLog.CallDetail =
tblDetails.CallDetail
WHERE tblIncidentLog.Date Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]
GROUP BY IIf(IsOther, "Other", tblIncidentLog.[CallDetail]);


--
Duane Hookom
Microsoft Access MVP

Please provide your actual SQL view. The expression looks fine.

SELECT tblIncidentLog.Date, tblIncidentLog.CallDetail, Count(tblIncidentLog.
CallDetail) AS CountOfCallDetail, tblDetails.IsOther
FROM tblIncidentLog, tblDetails
GROUP BY tblIncidentLog.Date, tblIncidentLog.CallDetail, tblDetails.IsOther
HAVING (((tblIncidentLog.Date) Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]) AND ((tblIncidentLog.CallDetail)
="Alcohol Abuse" Or (tblIncidentLog.CallDetail)="Allergic Reaction" Or
(tblIncidentLog.CallDetail)="Anxiety Attack" Or (tblIncidentLog.CallDetail)
="Childbirth - Delivery at Scene" Or (tblIncidentLog.CallDetail)="Childbirth -
Delivery Enroute" Or (tblIncidentLog.CallDetail)="Childbirth - Prior
Delivery" Or (tblIncidentLog.CallDetail)="Choking" Or (tblIncidentLog.
CallDetail)="CPR - Continued Through Transport" Or (tblIncidentLog.CallDetail)
="CVA / TIA Signs and Symptoms" Or (tblIncidentLog.CallDetail)="Diabetic
Emergency" Or (tblIncidentLog.CallDetail)="Difficulty Swallowing" Or
(tblIncidentLog.CallDetail)="Leg Infection" Or (tblIncidentLog.CallDetail)
="Medical Alarm - UTL" Or (tblIncidentLog.CallDetail)="Medication Reaction"
Or (tblIncidentLog.CallDetail)="Nose Bleed - Controlled" Or (tblIncidentLog.
CallDetail)="Nose Bleed - Uncontrolled" Or (tblIncidentLog.CallDetail)="GI
Bleed" Or (tblIncidentLog.CallDetail)="Miscarriage / Vaginal Bleeding" Or
(tblIncidentLog.CallDetail)="Diarrhea - Extended" Or (tblIncidentLog.
CallDetail)="General Weakness" Or (tblIncidentLog.CallDetail)="Misc. Medical
Incident" Or (tblIncidentLog.CallDetail)="Shaking" Or (tblIncidentLog.
CallDetail)="Cardiac Chest Pain" Or (tblIncidentLog.CallDetail)="Cardiac
Dysrhythmia" Or (tblIncidentLog.CallDetail)="Hypertension" Or (tblIncidentLog.
CallDetail)="Hypotension" Or (tblIncidentLog.CallDetail)="Irregular Heart
Beat" Or (tblIncidentLog.CallDetail)="SOB - Asthma" Or (tblIncidentLog.
CallDetail)="SOB - CHF" Or (tblIncidentLog.CallDetail)="SOB - COPD" Or
(tblIncidentLog.CallDetail)="SOB - Hyperventilation" Or (tblIncidentLog.
CallDetail)="11-44 - Terminated Enroute" Or (tblIncidentLog.CallDetail)
="Abdominal Pain" Or (tblIncidentLog.CallDetail)="Psychiatric - 5150 by Law
Enforcement" Or (tblIncidentLog.CallDetail)="Seizure" Or (tblIncidentLog.
CallDetail)="11-44 - Non-Initiation" Or (tblIncidentLog.CallDetail)="11-44 -
Terminated At Scene" Or (tblIncidentLog.CallDetail)="SOB - Other" Or
(tblIncidentLog.CallDetail)="CVA / TIA Signs and Symptoms" Or (tblIncidentLog.
CallDetail)="Dizziness" Or (tblIncidentLog.CallDetail)="Syncope" Or
(tblIncidentLog.CallDetail)="Altered LOC") AND ((tblDetails.IsOther)=If
("IsOther","Other",[CallDetail])));

 

[pushrodengine via AccessMonster.com]

SELECT CallDtl: IIf(IsOther, "Other", tblIncidentLog.[CallDetail]),

Count(tblIncidentLog.CallDetail) AS CountOfCallDetail,
FROM tblIncidentLog JOIN tblDetails ON tblIncidentLog.CallDetail =
tblDetails.CallDetail
WHERE tblIncidentLog.Date Between [Forms].[frmDistrictReport].[Start]
And [Forms].[frmDistrictReport].[End]
GROUP BY IIf(IsOther, "Other", tblIncidentLog.[CallDetail]);

I tried the expression and got another error that reads:

“Syntax error (missing operator) in query expression ‘CallDtl: IIf(IsOther,
“Otherâ€, tblIncidentLog.[CallDetail])’."