If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#2
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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. -- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#3
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
Thank you for relying.
I know exactly what I need to combined. How do I create a query with a subquery? Thanks 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#4
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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; -- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: Thank you for relying. I know exactly what I need to combined. How do I create a query with a subquery? Thanks 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#5
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
Thank you I'll give it a shot.
Duane Hookom wrote: 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] 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#6
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#7
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 "pushrodengine via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#8
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#9
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 "pushrodengine via AccessMonster.com" wrote: 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! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
#10
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200811/1 |
Thread Tools | |
Display Modes | |
|
|