Customized Query Reports

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 include a brief description of the query and e-mail AFIX SQL to HAwwad@cdc.gov and VFC SQL to Egelman@cdc.gov. Additional custom queries shared by users will be posted to this web page.

Consult Chapter 7 - Tool Bar Functions of the Users Guide [717 KB, 21 pages] for additional information to assist you with your CoCASA queries.

Creating Customized Reports [276 KB, 5 pages] is a short tutorial to guide you through creating a customized report using the SQL code on this page.

AFIX and Pre-2013 VFC Custom Query Reports

Report Title	Data Columns	SQL
Provider Information	Provider Name | Practice Type | Provider Type | City | County | VFC PIN | Contact First Name | Contact Last Name | Street Address 1 | Street Address 2	SELECT tblProviders.ProvName, tblPracticeTypeCodes.PracticeType, tblPracticeTypeCodes.VFCProviderType, tblProviders.City, tblProviders.County, tblProviders.VFCPIN, tblProviders.ContactFName, tblProviders.ContactLName, tblProviders.StreetAddress1, tblProviders.StreetAddress2 FROM tblPracticeTypeCodes INNER JOIN tblProviders ON tblPracticeTypeCodes.PracticeTypeID = tblProviders.PracticeTypeID;
Providers received any type site visit types	Provider Name | Practice Type | Provider Type | Region | User ID | VFC PIN | Visit Date | Visit Types	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;
Childhood AFIX Visit Information with coverage level rates	Provider Name | County | VFC PIN | Visit Type | Visit Date | User ID| Number of Charts | Age 0 – 3 | Age 4 – 12 | Assessment Method | Feedback Method | Coverage Levels	Name, 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 INNER JOIN tblVFCQustnnrs2009 ON tblVFCQuestionnaires.VFCQuestionnaireID = tblVFCQustnnrs2009.VFCQuestionnaireID) ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID WHERE (((tblVFCQuestionnaires.QuestionnaireName)="AFIX Visit" Or (tblVFCQuestionnaires.QuestionnaireName)="AFIX Visit and VFC Site Visit"));
Adolescent AFIX Visit Information with coverage level rates	Provider Name | County | VFC PIN | Visit Type | Visit Date | User ID| Number of Charts | Age 13 – 18 | Assessment Method | Feedback Method | Coverage Levels	SELECT tblProviders.ProvName, tblProviders.County, tblProviders.VFCPIN, tblVFCQuestionnaires.QuestionnaireName, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.UserID, tblVFCQuestionnaires.NumofChartsAnalysed, tblVFCQustnnrs2009.Age13to18, tblVFCQuestionnaires.AssessmentMethod, tblVFCQustnnrs2009.Fdbkmthd, tblVFCQustnnrs2009.Covlvl3HepB, tblVFCQustnnrs2009.Covlvl2MMR, tblVFCQustnnrs2009.Covlvl2Varicella, tblVFCQustnnrs2009.Covlvl1Td_Tdap, tblVFCQustnnrs2009.Covlvl1MCV4, tblVFCQustnnrs2009.Covlvl3HPV FROM (tblProviders INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID) INNER JOIN tblVFCQustnnrs2009 ON tblVFCQuestionnaires.VFCQuestionnaireID = tblVFCQustnnrs2009.VFCQuestionnaireID WHERE (((tblVFCQuestionnaires.QuestionnaireName)="AFIX Visit" Or (tblVFCQuestionnaires.QuestionnaireName)="AFIX Visit and VFC Site Visit")) ORDER BY tblVFCQustnnrs2009.Age13to18;
Assessment Details	Provider Name | County | VFC PIN | From Age Range | To Age Range | AsOfDate | Assessment Date | Assessment Name	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;
Issues Requiring Corrective Action	Provider Name | VFC PIN | Visit Date | Non-Compliant Issues	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCQuestionnaires.VisitDate, tblVFCQuestionnaires.NonCompliantIssues FROM tblProviders INNER JOIN tblVFCQuestionnaires ON tblProviders.ProvID = tblVFCQuestionnaires.ProvID ORDER BY tblProviders.ProvName;
Site Visit’s Questionnaire other answer choice text	Provider Name | VFC PIN | Visit Date | Questionnaire Name | AFIX Contact Text | AFIX Visit Follow-up Text | VFC Site Visit Follow-up  Text | VFC Enrollment Visit Text | Educational Visit Text | VFC Secondary Educational Follow-up Text | VFC Tertiary Educational Follow-up	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;

Top of Page

2013 VFC Custom Query Reports

Report Title	Data Columns	SQL
Provider type, visit type and date of visit	Provider Name Practice Type VFC Provider Type Region User ID VFC Pin Visit Date VFC Visit Name/Type	SELECT tblProviders.ProvName, tblPracticeTypeCodes.PracticeType, tblPracticeTypeCodes.VFCProviderType, tblProviders.Region, tblVFCVisits.UserID, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM (tblPracticeTypeCodes INNER JOIN tblProviders ON tblPracticeTypeCodes.PracticeTypeID = tblProviders.PracticeTypeID) INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID;
Provider type, visit type and date of visit within specified date range *change date range as needed	Provider Name Practice Type VFC Provider Type Region User ID VFC Pin Visit Date VFC Visit Name/Type	SELECT tblProviders.ProvName, tblPracticeTypeCodes.PracticeType, tblPracticeTypeCodes.VFCProviderType, tblProviders.Region, tblVFCVisits.UserID, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM (tblPracticeTypeCodes INNER JOIN tblProviders ON tblPracticeTypeCodes.PracticeTypeID = tblProviders.PracticeTypeID) INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VisitDate Between #01/01/2013# AND #03/05/2013#;
Issues Requiring Corrective Action	Provider Name VFC Pin Visit Date Non-Compliant Issues	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.NonCompliantIssues FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE tblVFCVisits.NonCompliantIssues <> '' ORDER BY tblProviders.ProvName;
Issues Requiring Corrective Action within specified date range *change date range as needed	Provider Name VFC Pin Visit Date Non-Compliant Issues (issues requiring corrective action)	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.NonCompliantIssues FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE tblVFCVisits.NonCompliantIssues <> '' AND (VisitDate Between #01/01/2013# AND #03/05/2013# ) ORDER BY tblProviders.ProvName;
VFC Contacts and Enrollment Visits combined list	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text VFC Contact Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VFCEnVisit = True OR VFCContactText <> '' ORDER BY tblProviders.ProvName;
VFC Contacts and Enrollment Visits combined list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text VFC Contact Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCEnVisit = True OR VFCContactText <> '') AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
VFC Contacts list	Provider Name VFC Pin Visit Date VFC Visits Name VFC Contact Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VFCContactText <> '' ORDER BY tblProviders.ProvName;
VFC Contacts list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visits Name VFC Contact Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCContactText <> '') AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
VFC Enrollment Visits list	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VFCEnVisit = True ORDER BY tblProviders.ProvName;
VFC Enrollment Visits list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCEnVisit = True) AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
VFC Educational Visits list	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, IIF([VFCComplianceVisit] = TRUE, 'VFC Compliance Visit (Vaccine Management Education provided as a part visit)', tblVFCVisits.VFCVisitName) as [Visit Name] FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCVaccMgmtEdVisit = True OR (VFCComplianceVisit = TRUE AND VaccMgmtEdExists = TRUE)) ORDER BY tblProviders.ProvName;
VFC Educational Visits list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, IIF([VFCComplianceVisit] = TRUE, 'VFC Compliance Visit (Vaccine Management Education provided as a part visit)', tblVFCVisits.VFCVisitName) as [Visit Name] FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCVaccMgmtEdVisit = True OR (VFCComplianceVisit = TRUE AND VaccMgmtEdExists = TRUE)) AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
Providers receiving at least one VFC related visit using the 2013 VFC Evaluation Tab	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text VFC Contact Visit Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID ORDER BY tblProviders.ProvName;
Providers receiving at least one VFC related visit using the 2013 VFC Evaluation Tab within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name VFC Enrollment Visit Text VFC Contact Visit Text	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName, tblVFCVisits.VFCEnVisitText, VFCContactText FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
VFC Storage and Handling Visits list	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VFCStorHandVisit = True ORDER BY tblProviders.ProvName;
VFC Storage and Handling Visits list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCStorHandVisit = True) AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;
VFC Compliance Visits list	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE VFCComplianceVisit= True ORDER BY tblProviders.ProvName;
VFC Compliance Visits list within specified date range *change date range as needed	Provider Name VFC Pin Visit Date VFC Visit Name	SELECT tblProviders.ProvName, tblProviders.VFCPIN, tblVFCVisits.VisitDate, tblVFCVisits.VFCVisitName FROM tblProviders INNER JOIN tblVFCVisits ON tblProviders.ProvID = tblVFCVisits.ProvID WHERE (VFCComplianceVisit= True) AND (VisitDate Between #01/01/2013# AND #03/05/2013#) ORDER BY tblProviders.ProvName;

Top of Page