Skip directly to search Skip directly to A to Z list Skip directly to navigation Skip directly to page options Skip directly to site content

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 CoCASA Users Guide for additional information to assist you with your CoCASA queries.
 

Creating Customized Reports [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 TitleData ColumnsSQL
Provider InformationProvider Name | Practice Type | Provider Type | City | County | VFC PIN | Contact First Name | Contact Last Name | Street Address 1 | Street Address 2SELECT 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 typesProvider Name | Practice Type | Provider Type | Region | User ID | VFC PIN | Visit Date | Visit TypesSELECT 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 ratesProvider Name | County | VFC PIN | Visit Type | Visit Date | User ID| Number of Charts | Age 0 – 3 | Age 4 – 12 | Assessment Method | Feedback Method | Coverage LevelsName, 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 ratesProvider Name | County | VFC PIN | Visit Type | Visit Date | User ID| Number of Charts | Age 13 – 18 | Assessment Method | Feedback Method | Coverage LevelsSELECT 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 DetailsProvider Name | County | VFC PIN | From Age Range | To Age Range | AsOfDate | Assessment Date | Assessment NameSELECT 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 ActionProvider Name | VFC PIN | Visit Date | Non-Compliant IssuesSELECT 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 textProvider 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-upSELECT 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 TitleData ColumnsSQL
Provider type, visit type and date of visitProvider 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 ActionProvider 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 listProvider 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 listProvider 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 listProvider 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 listProvider 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 TabProvider 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 listProvider 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 listProvider 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
Top