Programs and Tools:
CoCASA: Custom CoCASA Queries
CoCASA pages:
Queries and SQL
Data entered into CoCASA can be exported into Microsoft Access where users can create custom queries to view and analyze data in ways not readily accessible in CoCASA. Directions for creating custom queries using Microsoft Access are provided in the CoCASA 501 training which is available for viewing under the CoCASA Training Resources page.
Provided below are the SQL (structured query language) for creating custom queries discussed during the CoCASA 501 training. The SQL listed are meant to be "copied" and then "pasted" into the "SQL view" window in Access when creating a query in the design view. Although we have provided language for select custom queries below, we are not able to provide technical support for users with Access related questions.
If you develop separate custom queries that may be particularly useful to others, please e-mail the SQL and a brief description to MBHuynh@cdc.gov. Additional custom queries shared by users will be posted to this web page.
| # | Custom Query Name & SQL |
|---|---|
| 1 | Query Name: Basic Provider Information |
| 2 | Query Name: Provider type, visit type and date SQL: SELECT tblProviders.ProvName, tblPracticeTypeCodes.PracticeType, tblPracticeTypeCodes.VFCProviderType, tblProviders.Region, tblVFCQuestionnaires.UserID, tblProviders.VFCPIN, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.QuestionnaireName FROM (tblPracticeTypeCodes INNER JOIN tblProviders ON tblPracticeTypeCodes.PracticeTypeID = tblProviders.PracticeTypeID) INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID; |
| 3 | Query Name: Childhood AFIX Visit Information, including rates
SQL: SELECT tblProviders.ProvName, tblProviders.County, tblProviders.VFCPIN, tblVFCQuestionnaires.QuestionnaireName, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.UserID, tblVFCQuestionnaires.NumofChartsAnalysed, tblVFCQustnnrs2009.Age0to3, tblVFCQustnnrs2009.Age4to12, tblVFCQuestionnaires.AssessmentMethod, tblVFCQustnnrs2009.Fdbkmthd, tblVFCQuestionnaires.CovLvl431331, tblVFCQuestionnaires.CovLvl4313314 FROM (tblProviders INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID=tblVFCQuestionnaires.ProvID) INNER JOIN tblVFCQustnnrs2009 ON tblVFCQuestionnaires.VFCQuestionnaireID=tblVFCQustnnrs2009.VFCQuestionnaireID WHERE (((tblVFCQuestionnaires.QuestionnaireName)="AFIX Visit")); |
| 4 | Query Name: Adolescent AFIX Visit Information, including rates
|
| 5 | Query Name: Assessment Details
SQL: SELECT tblProviders.ProvName, tblProviders.County, tblProviders.VFCPIN, tblAssessments.FromAgeRangeMonths, tblAssessments.ThruAgeRangeMonths, tblAssessments.AsOfDate, tblAssessments.AssessmentDate, tblAssessments.AssessmentName FROM tblProviders INNER JOIN tblAssessments ON tblProviders.ProvID = tblAssessments.ProvID; |
| 6 | Query Name: Issues Requiring Corrective Action
SQL: SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.NonCompliantIssues FROM tblProviders INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID ORDER BY tblProviders.ProvName; |
| 7 | Query Name: Other visits text
SQL: SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.QuestionnaireName, tblVFCQuestionnaires.AFIXContactText, tblVFCQustnnrs2009.AFIXVstFlwupText, tblVFCQuestionnaires.VFCStVsFlwupText, tblVFCQuestionnaires.VFCEnVisitText, tblVFCQuestionnaires.EducVisitText, tblVFCQustnnrs2009.VFCSecEdFlwupText, tblVFCQustnnrs2009.VFCTertEdFlwupText FROM (tblProviders INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID) INNER JOIN tblVFCQustnnrs2009 ON tblVFCQuestionnaires.VFCQuestionnaireID = tblVFCQustnnrs2009.VFCQuestionnaireID ORDER BY tblProviders.ProvName; |
This symbol means you are leaving the CDC.gov Web site. For more information, please see CDC's Exit Notification and Disclaimer policy.
File Formats: All viewers, players, and plug-ins used on this site can be downloaded from the file formats page. (For example: Adobe Acrobat Reader for pdf files, Windows Media Player for audio and video files, PowerPoint Viewer for presentation slides, etc.)
Content last reviewed on June 4, 2009
Content Source: National Center for Immunization and Respiratory Diseases
