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 |
#11
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
"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? -- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: 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 |
#12
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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. Add a yes/no field named "IsOther". user to make the judgment on whether entry is “other”". I was assuming the user would have to answer for “IsOther”. Your previous reply in this string had a query defined as "other". How did you gon from defining other as to not knowing what "other" is? 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#13
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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. -- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: 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. Add a yes/no field named "IsOther". user to make the judgment on whether entry is “other”". I was assuming the user would have to answer for “IsOther”. Your previous reply in this string had a query defined as "other". How did you gon from defining other as to not knowing what "other" is? 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#14
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#15
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
From a previous post: Then in your query, add the table and GROUP BY the
expression: CallDtl: IIf(IsOther, "Other", [CallDetail]) -- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#16
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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” -- Message posted via http://www.accessmonster.com |
#17
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
Please provide your actual SQL view. The expression looks fine.
-- Duane Hookom Microsoft Access MVP "pushrodengine via AccessMonster.com" wrote: 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” -- Message posted via http://www.accessmonster.com |
#18
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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]))); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#19
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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 "pushrodengine via AccessMonster.com" wrote: 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]))); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
#20
|
|||
|
|||
Graphing an "Other" section in a Pie Graph
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])’." -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200812/1 |
Thread Tools | |
Display Modes | |
|
|