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

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

 

External Web Site Policy This symbol means you are leaving the CDC.gov Web site. For more information, please see CDC's Exit Notification and Disclaimer policy.

Copyrighted images: Images on this website which are copyrighted were used with permission of the copyright holder and are not in the public domain. CDC has licensed these images for use in the materials provided on this website, and the materials in the form presented on this website may be used without seeking further permission. Any other use of copyrighted images requires permission from the copyright holder.

 
Contact CoCASA:
Contact Us:
  • Centers for Disease Control and Prevention
    1600 Clifton Rd
    Atlanta, GA 30333
  • 800-CDC-INFO
    (800-232-4636)
    TTY: (888) 232-6348
    Contact CDC-INFO
USA.gov: The U.S. Government's Official Web PortalDepartment of Health and Human Services
Centers for Disease Control and Prevention   1600 Clifton Road Atlanta, GA 30329-4027, USA
800-CDC-INFO (800-232-4636) TTY: (888) 232-6348 - Contact CDC–INFO
A-Z Index
  1. A
  2. B
  3. C
  4. D
  5. E
  6. F
  7. G
  8. H
  9. I
  10. J
  11. K
  12. L
  13. M
  14. N
  15. O
  16. P
  17. Q
  18. R
  19. S
  20. T
  21. U
  22. V
  23. W
  24. X
  25. Y
  26. Z
  27. #