Sample Queries and Available Reports

This page contains various sample Data Warehouse queries, as well as a listing of available reports.

Sample Queries

There are various sample query tutorials available for download. Most of the query tutorials require Adobe Reader to view and print the PDF documents.

The sample queries available include:

[ Back to Top ]

Available Reports

Available reports, organized by subject. Click on the link of interest to see what reports are available.

ASR Reports

For a printable version of any report once you are in EIS, click on the "View as PDF" button in the lower left area of the screen after running the report. If you already have obtained access to EIS, you may login here.

ACOM Access List
Listing of users with ACOM access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

ASR User
Returns AIS applications and systems access for a single user with ASR1, ASR2, ASR3, ASR4 listed.
Select the Access and Security Representative folder, then the User Information folder.

ASR user list
Returns a list for users for the ASR logged in. Use Report Navigator to jump to value selected.
Grouped by: ASR1
Sorted by: ASR2, ASR3, ASR4, User Name
Select the Access and Security Representative folder, then the User Information folder.

BCOM Access List
Listing of users with BCOM access.
Sorted by: user name.
Select the Access and Security Representative folder, then the Access by System folder.

CCOM Access List
Listing of users with CCOM access.
Sorted by: user name.
Select the Access and Security Representative folder, then the Access by System folder.

Cold Access List
Listing of users with Cold access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

Data Warehouse Users
Listing of all data warehouse users and their current access on the warehouse.
Sorted by: database name, user name.
Select the Access and Security Representative folder, then the Data Warehouse folder.

DCOM Access List
Listing of users with DCOM access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

EDDS User List
List of EDDS access for a single user.
Select the Access and Security Representative folder, then the EDDS folder.

EDDS User Access List
List of eDDS users and their access.
Use Report Navigator to jump to value selected.
Grouped by: user name
Sorted by: user name
Select the Access and Security Representative folder, then the EDDS folder.

EIS Access List
List of users with EIS access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

FIT Access List
List of users with FIT access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

IBIS Access
Returns IBIS access for a single user.
Sorted by: path
Select the Access and Security Representative folder, then the Access by System folder.

ISIS Access
Returns ISIS access for a single user.
Sorted by: path
Select the Access and Security Representative folder, then the Access by System folder.

Roscoe Access List
List of users with Roscoe Access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

SecurID Token user list
Returns secure token ID information for a list of users.
Use Report Navigator to jump to value selected.
Grouped By: Expiration Date
Sorted By: User Name
Select the Access and Security Representative folder, then the SecurID Token Expiration folder.

Testais Access List
List of users with TestAIS access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

TSO Access List
List of users with TSO access.
Sorted by: user name
Select the Access and Security Representative folder, then the Access by System folder.

[ Back to Top ]

Sample Queries with MS Access Download

Early Progress Report (EPR) Sample Queries

Early Progress Report (EPR): The EPR was designed to capture student performance early in the semester. Primarily intended to help first-year students, Commonwealth Campuses may opt to have all students involved in the reporting process. Only students in the designated population will appear in the tables. These queries were designed to compare the number of expected reports to the number that were actually submitted. This is useful to determine the level of participation in a particular organization or unit and to track the progress of individual faculty toward completing all the expected reports. Instructors assigned to the course in the student information system (ISIS) may submit reports using eLion Early Progress Report.

These queries are meant to be samples that are customized by end users. They include prompts for pieces of information to refine the output, but users can replace them with criteria that they use most often. For example, the prompt for semester can be replaced with the current semester and changed for the next EPR cycle.

Tables: The following tables will need to be linked from the Student data warehouse database in order for all of these queries to run properly.

dbo_epr_course
dbo_instructor_bio
dbo_uf_csec_registrar_instructor
dbo_uf_tcm

SQL Query Statements:

Evaluations Completed by Course for a UP College
Evaluations Completed by Section for a Campus
Evaluations Completed by Instructor for a Course Name

Evaluations Completed by Course for a UP College: The output from this query shows all courses for which at least one report is expected. The user is prompted for a semester and college. Output is sorted by the academic department recorded in ISIS and the course name and number. Counts are displayed for the number of sections and students, and output includes an indicator for whether all reports were submitted.

SELECT dbo_epr_course.semester, dbo_uf_tcm.college, dbo_uf_tcm.academic_dept, dbo_epr_course.course, Count(dbo_epr_course.schedule_number) AS [Sections Expected], Sum(IIf([dbo_epr_course]![evaluation_status]="Completed",1,0)) AS [Sections Completed], Sum(dbo_epr_course.student_evaluations_expected) AS [Students Expected], Sum(dbo_epr_course.student_evaluations_completed) AS [Students Completed], IIf([dbo_epr_course]![student_evaluations_expected]=[dbo_epr_course]![student_evaluations_completed],"Yes","No") AS Completed FROM dbo_epr_course LEFT JOIN dbo_uf_tcm ON (dbo_epr_course.schedule_number = dbo_uf_tcm.schedule_number) AND (dbo_epr_course.semester = dbo_uf_tcm.semester) WHERE (((Left([dbo_epr_course]![location],2))="UP")) GROUP BY dbo_epr_course.semester, dbo_uf_tcm.college, dbo_uf_tcm.academic_dept, dbo_epr_course.course, IIf([dbo_epr_course]![student_evaluations_expected]=[dbo_epr_course]![student_evaluations_completed],"Yes","No") HAVING (((dbo_epr_course.semester)=[Enter Semester (like 200910fa for fall 2009 or 200910sp for spring 2010):  ]) AND ((dbo_uf_tcm.college)=[Enter College Code (xx):])) ORDER BY dbo_uf_tcm.academic_dept, dbo_epr_course.course;

Evaluations Completed by Section for a Campus: The output from this query shows all courses for which at least one report is expected. The user is prompted for a semester and a campus. Output is sorted by the course name, number and section. Counts are displayed for the number of students with expected and completed reports, and output includes an indicator for whether all reports were submitted. This query was not designed to be run for University Park.

SELECT dbo_epr_course.semester, dbo_epr_course.location, dbo_epr_course.course, dbo_epr_course.section, dbo_epr_course.student_evaluations_expected AS [Students Expected], dbo_epr_course.student_evaluations_completed AS [Students Completed], IIf([Students Expected]=[Students Completed],"Yes","No") AS Completed FROM dbo_epr_course WHERE (((dbo_epr_course.semester)=[Enter Semester (like 200910fa for fall 2009 or 200910sp for spring 2010):  ]) AND ((Left([dbo_epr_course]![location],2))=[Enter Campus (xx):])) ORDER BY dbo_epr_course.course, dbo_epr_course.section;

Evaluations Completed by Instructor for a Course Name: The output from this query shows all courses for which at least one report is expected. The user is prompted for a semester, campus, and course name. Output is sorted by instructor name, course name, number and section. Counts are displayed for the number of students with expected and completed reports, and output includes an indicator for whether all reports were submitted. Due to the linking of multiple tables, this query is somewhat slow.

SELECT dbo_epr_course.semester, dbo_instructor_bio.REPORT_DISPLAY_NAME, dbo_epr_course.location, dbo_uf_tcm.college, dbo_epr_course.course, dbo_epr_course.section, dbo_epr_course.student_evaluations_expected AS [Students Expected], dbo_epr_course.student_evaluations_completed AS [Students Completed], IIf([dbo_epr_course]![student_evaluations_expected]=[dbo_epr_course]![student_evaluations_completed],"Yes","No") AS Completed FROM ((dbo_epr_course LEFT JOIN dbo_uf_tcm ON (dbo_epr_course.schedule_number = dbo_uf_tcm.schedule_number) AND (dbo_epr_course.semester = dbo_uf_tcm.semester)) LEFT JOIN dbo_uf_csec_registrar_instructor ON (dbo_epr_course.schedule_number = dbo_uf_csec_registrar_instructor.schedule_number) AND (dbo_epr_course.semester = dbo_uf_csec_registrar_instructor.semester)) LEFT JOIN dbo_instructor_bio ON (dbo_uf_csec_registrar_instructor.semester = dbo_instructor_bio.CODE_UNIV_YR_SEM) AND (dbo_uf_csec_registrar_instructor.instructor_id = dbo_instructor_bio.NUMB_CSEC_INSR) WHERE (((dbo_epr_course.semester)=[Enter Semester (like 200910fa for fall 2009 or 200910sp for spring 2010):  ]) AND ((Left([dbo_epr_course]![location],2))=[Enter Campus Code (xx):]) AND ((Left([dbo_epr_course]![course],5))=[Enter a Course Name (aaaaa):])) ORDER BY dbo_instructor_bio.REPORT_DISPLAY_NAME, dbo_epr_course.course, dbo_epr_course.section;

EPR Assessment Database: This database contains four forms that utilize information from several tables in the student database to provide operational information at the course, instructor, and student level regarding the Early Progress Report (EPR).

The forms are:

  1. frmEvalsCompleted – Pulls data that indicates how many sections of a course are set to participate in the EPR process and how many actually did.
  2. frmInstructorCompletion – Pulls data that indicates how many students in a course are expected to receive an EPR indicator and how many did, along with the name of the instructor teaching the course.
  3. frmIndividualStudent – Pulls a record for each instructor comment received by student along with student information.
  4. frmSatisfactoryCounts – Pulls data that indicates, by course, how many students received an unsatisfactory indicator.

Note that you will need access to the following student database tables to be able to run these forms:

dbo_epr_course
dbo_epr_enrolled_student
dbo_epr_student
dbo_instructor_bio
dob_uf_csec_registrar_instructor
dbo_uf_student_sem
dbo_uf_tcm

Also note that the name you have set for your ODBC Data Warehouse connection may be different than what is in this database. If so, then you will have to re-link the tables.

The MS Access database that accompanies this sample query can be downloaded here.  

Last updated:  9/22/10

Campus Class List

This database allows an individual to run Class Lists for an entire campus at once.

Example of class list report

This database is campus, semester, class, section and delivery specific.

The MS Access database that accompanies this sample query can be downloaded here.

Campus Change of Location (CHOA)

This database and set of queries will allow you to look for students who changed campus location in one of several ways (i.e. semester to semester or semester to current or semester to future).

Determining the Right Query

To begin, you need to choose which query you need to run:

List of available queries

When you have determined which query to run, highlight it and select the DESIGN button to the right of the query.

Editing Your Query

After you push the DESIGN button, you will see the design of the query:

Design of the query chosen

Note: DBO_STUDENT_SEM_F indicated DBO_STUDENT_SEM_FUTURE

Filling in the Criteria and Running the Query

Now, fill in the criteria specific to your needs as shown above. Close the query design when complete, re-highlight the query, the select the OPEN button to the right of the query.

Viewing Your Results

Your results are now stored in a table titled either tbl Previous to Future Change of Location, tbl Previous to Current Change of Location, or tbl Current to Future Change of Location.

You can find your table by selecting the tables tab on Access.

Example of table generated

Note: Name and student number fields excluded for privacy purposes

The MS Access database that accompanies this sample query can be downloaded here.

Conflicts Matrix

This database can be used to determine the enrollment of a specific course, along with generating a lengthy listing of other courses students in a specific course are enrolled in during a given semester. The information is useful for scheduling evening exams, final exams, and other similar events.

Sample conflicts matrix

This database is campus and semester specific.

The MS Access database that accompanies this sample query can be downloaded here.

University Facility/Room Scheduling Records for a Specific Campus

This database is linked to the facility database on the AIS Data Warehouse and consists of two data tables. The facility data table accesses the facility records for a given campus, and the event data table accesses the room scheduling records for a given campus per semester. The combination of these data tables allows the user to determine room/building availability.

These sample queries are intended to help the user properly access the data in order to retrieve the desired results from a database query.

FACILITY query will return a list of rooms that meet the users requirements. The query can be refined to provide a list of rooms based on any combination of the following:

SCHEDULED ROOMS query will return a list of rooms that are scheduled for courses or supporting events. The query can be refined to provide rooms based on any combination of the following:

Select the desired query from the Queries Tab in the Facility Database and select the DESIGN button. The query will be displayed in the Design View. The Design View is where the query can be modified to retrieve the desired population of data records.

Note: When using the semester field for the Room Scheduling Information data table, use "S1", not "SU". When using Room Scheduling Information data table (facility) use "SU", not "S1".

List of queries in the facility database

Query to Find Rooms with a Particular Technology Code

Using the Facility query as an working example, the group of returned rooms will meet the following criteria:

Locate the field campus enter UP (University Park campus)

Locate the field room_semester_end enter null (field is blank - no ending semester) on the next line down, for the same field, enter >= 200102SP (ending semester is equal to or greater than Spring 2002)

Note: These two entries are necessary for the first field due to the format of the data. A facility record with no ending semester means the room is in effect indefinitely. When an ending semester is entered for a facility record, the room is active until the end of that semester. Both values are needed to ensure all possible records are checked.

Locate the field room_tech_code and enter ITEC (technology code for instructional technology)

Note: The values entered for campus and tech_code need to be repeated on the second line for each of those fields because of the multiple values for the semester_end field. This guarantees the campus and tech_code values are the same for both of the values of semester_end.

Adjusting the fields for the query

Once the desired criteria has been entered, execute the query by clicking on the run icon ( ! ) or save the query, close the query window and select the OPEN button from the QUERIES tab. The executed query will return a list of records that meet the above criteria.

Query to Determine Rooms With Scheduling Blocks During a Range of Dates

Using the Facility query again in design mode, the next example will return records that meet the following criteria:

Locate the field campus and enter UP (University Park campus)

Locate the field room_block_date_begin1 and enter the date desired. For purposes of this example, the date will be May 7, 2002, entered as > 5/7/02 (Any room with scheduling blocks greater than 5/7/02)

Due to the facility records having up to three possible blocks listed, enter the same date criteria for fields room_block_date_begin2 and room_block_date_begin3

Note: Entering search criteria for Block-1, Block-2 and Block-3 on the same line will return only the records that have block information listed on all three block fields (Block-1 and Block-2 and Block-3).

Entering search criteria for Block-1, Block-2 and Block-3 on different lines will return the records that have block information listed on any of the three block fields (Block-1 or Block-2 or Block-3). In this case, remember to repeat the campus code for each line that has room_block_date_begin information entered on the query. This will ensure that all three lines of block information are pulled from the same campus.

Entering room block date ranges into query

Once the desired criteria has been entered, execute the query by selecting the run icon ( ! ) or save the query, close the query window and select the OPEN button from the QUERIES tab. The executed query will return a list of records that meet the above criteria.

Query to Determine Room Availability

Using the Event query as an example, return records that meet the following criteria:

Locate the field campus and enter UP (University Park campus)

Locate the field event_scheduling_day and enter MON (three character abbreviation for day of the week)

Locate the field event_date_scheduled and enter null on the first line and enter the specific date to search for on the second line, 02/18/02. Both values are needed due to the format of the data. Semester long scheduling records are written with one record for each day of the week the course/event meets. Courses/events that meet for part of the semester have one record written for date scheduled. The null entry will find the semester long records for the day of the week and the date entry is used to check for any records scheduled for the date entered.

Locate the field building_name and enter WILLARD (name of the building)

Locate the field room_number and enter Like 2** (all rooms that begin with 2, meaning second floor)

Adjusting query for room availability

Once the desired criteria has been entered, execute the query by selecting the run icon ( ! ) or save the query, close the query window and select the OPEN button from the QUERIES tab. The executed query will return a list of records that meet the above criteria.

Query to Determine Schedule Events by Department/Event Code

Using the Events by Deptartment/Event Code query as an example, return records that meet the following criteria:

Locate the field campus and enter UP (University Park campus)

Locate the field semester and enter the eight character semester code desired, for this example, 200102SP

Locate the field event_code and enter the department or event code desired, MATH

Adjusting the query to find events by department or event code

Once the desired criteria has been entered, execute the query by selecting the run icon (!) or save the query, close the query window and select the OPEN button from the QUERIES tab. The executed query will return a list of records that meet the above criteria.

The scheduled events will be sorted in the following order:

The MS Access database that accompanies this sample query can be downloaded here.

Campus Specialized Class List for Selected Math Courses (SYSIF055)

This set of queries reads the student and applicants databases and selects students enrolled in select math courses, including Math 001, 002, 003, 004, 021, 022, 026, 030, 040, 041, 110, all 140's and all 141's for the designated course semester. The FTCAP scores and all completed math courses and grades are printed on the report for each student.

Start by selecting the CLICK HERE TO BEGIN button for instructions.

Button to begin

Next, select the QUERIES TAB, highlighting the first query, #1 - qry Create Campus Specific Student Listing. Once this is highlighted, select the OPEN button to the right.

Select query number 1 from list

Once this query has run, continue to run the queries in the order they are listed. If you are prompted to delete a table or paste rows into a table, then simply answer "YES."

You will also be asked for two sets of criteria throughout the process of running these queries. You will be asked for campus code and current semester code. The correct criteria can be found below.

For campus code, you need to enter the two-character campus code:

For current student semester, you need to enter the eight-character semester code:

Note: Query #2 is extracting information from the TCM table. If you are running a report for a summer, when prompted for the semester on query #2, use S1 instead of SU.

Once you have run the last query, select the REPORTS TAB; then highlight rptSYSIF055 and select the PREVIEW button to the left. This will provide you with the actual SYSIF055 Report.

Selecting report from reports tab

Sample report for SYSIF055

This database is campus and semester specific.

The MS Access database that accompanies this sample query can be downloaded here.

Previously Attending A Campus (SYSIF120)

This database develops a query that will list students currently located at University Park, who previously attended another campus location.

Queries #1-#3

Listing of the three different queries

While running queries #1 to  #3, you will be prompted by an input box to enter the campus criteria in the two-digit character format (e.g., BK, UP, AA).

Prompt for entering two character campus code

Viewing Report

After running query #3, your results will be displayed.

Sample report of students previous attending a campus

Note: Student number fields were excluded for privacy and security of student information.

The MS Access database that accompanies this sample query can be downloaded here.

First-Year Students Not Taking FYS Courses

This database can be used to determine which first-year baccalaureate students, registered for the current semester in a specific college or at a specific campus, did not take a first-year seminar class.

Start by selecting the button you see when the database opens:

Instructions button

Once you have selected the above button, you will see a table like shown below, but without the red boxes. The red boxes are shown here to indicate where you need to input your criteria:

Adjusting the fields in the query table

COLLEGE CODE: Use to receive college/department specific data (if you do not want a specific college, leave this blank). Examples include:

CAMPUS CODE: Use to receive campus specific data.  Examples include:

ADMIT SEMESTER: Use to receive specific students that were admitted in a specific semester. Examples include:

Note: This query also contains the field code_stud_majr_lvl which we have set with "A" as the criteria. This means that the query will only pull undergraduate baccalaureate degree students since undergraduate associate degree students are not required to take a first-year seminar.

Now close this table and run the four queries as seen on your screen (and below) in the order in which they appear.

List of queries to be run

Here is an example of the table that shows students that have taken a First-Year Seminar:

Sample report of students that have taken a first year seminar

Here is an example of the table that shows students that have not taken a First-Year Seminar:

Sample report showing students who have not taken a first year seminar

This database is campus, college, semester, and admission semester specific.

The MS Access database that accompanies this sample query can be downloaded here.

Grade Reporting for Campus, College and Departments

This database is linked to the student and isiscodesets databases on the AIS Data Warehouse. There are four data tables used to create the nine reports that are available by downloading this database. The dbo_ccoll, dbo_cdept, and cbo_clocn data tables, from the isiscodesets database, are used to display report description names for campus location, college, and department code values. The dbo_tcm table, from the student database, is used to determine if a Grade Scan Form has been assigned for the course for the current semester, to exclude courses with no expected grades, and to filter the end date for the course to be less than the current system date of the machine running the query. The reports are based on six queries (q_Campus, q_College, q_Department, q_Campus_No_Activity, and q_College_No_Activity). These six queries extract data from the dbo_tcm table, the three isiscodesets tables, and build a number of calculated fields. The reports allow the user to determine the status of grades entered for a campus, college, or department.

Queries

These demonstration queries are intended to help the user access the data in order to retrieve a desired result from a database query.

q_Prompt_Campus query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following:

q_Prompt_College query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following:

q_Prompt_Department query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following:

q_Campus_No_Activity query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following:

q_College_No_Activity query will return a list of course sections that are determined by the criteria entered by the user. The query can be refined to provide a list of course sections based on any combination of the following:

q_Extract_tcm query will return a list of course sections that are determined by the criteria entered by the user. This query is invoked by the other queries and joined with additional tables. The calculated code_loc field is derived for report display purposes and joined with the dbo_clocn table in the other queries. The query can be refined to provide a list of course sections based on any combination of the following:

The queries can be modified to change the criteria in order to retrieve a specific population of records by selecting the query from the Queries Tab in the Grade Report Database and selecting the "Design" button (i.e., you can remove the prompt criteria and replace the prompt with a code for a campus, college, or department value). The query will be displayed in the Design View. The Design View is where the query can be modified to retrieve the desired population of data records.

Note: The calculated code_loc field (Mid([dbo_tcm]![code_csec_loc],1,2)) is derived to create the join with the dbo_clocn data table.

Selecting the specific query

Adjusting the various query fields

Various prompts for entering query information

Reports to Determine Courses that have Ended with No Grading Activity

The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed, it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button.

Select one of the following reports:

The campus level report will prompt the user for a semester and campus code. The college level report will prompt the user for a semester, campus code, and a college code.

Note: The date_csec_grade_form_produced field criteria is set to "Is Not Null." This criteria validates that a scan form has been produced for the course(s) and that the course is ready for grading.  The calculated days field ([qnty_csec_stud_graded]=0,DateDiff("d",[date_csec_end],Now()),Null) criteria is set to "Is Not Null." This field is calculated to validate that no grades have been entered for the course and calculates the number of days from the course end date. The criteria filters the query to select only the records with no grading activity.

Adjusting query for q_Campus_No_Activity

Once the prompted criteria has been entered, the report will run and display.

Sample q_Campus_No_Activity report

Report to Display Grade Entry Activity Detail

The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button.

Select one of the following reports:

The campus level report will prompt the user for a semester and campus code. The department level report will prompt the user for a semester, campus code, and a department code. The Campus_Level_Report_for_Grade_Entry_by_College_Detail report groups the data by college.

Note: The qnty_csec_expected_grades field criteria is set to "<>0." This criteria validates that a course has at minimum one student for grading (students can have a status of registered, late drop, or withdrawal). The calculated code_loc field (Mid([q_Extract_tcm]![code_csec_loc],1,2)) is derived to create the join with the dbo_clocn data table. The calculated count fields are used to sum counts for the report.

Adjusting the q_Campus query

Making the final adjustments to the query

Once the prompted criteria has been entered, the report will run and display.

Sample report for grade entry

Report to Display Grade Entry Activity Summary Counts

The reports can be viewed by selecting the Reports Tab in the Grade Report Database and selecting the "Preview" button. Once the a report is displayed it can be either printed or exported to Microsoft Word. If you would like to modify the report design, select the "Design" button.

Select one of the following reports:

The campus level report will prompt the user for a semester and campus code. The college level report will prompt the user for a semester and college code. The department level report will prompt the user for a semester, campus code, and a department code. The Campus_Level_Report_for_Grade_Entry_by_College_Summary report groups the data by college.

Note: The qnty_csec_expected_grades field criteria is set to "<>0." This criteria validates that a course has at minimum one registered student for grading (students can have a status of registered, late drop, or withdrawal). The calculated code_loc field (Mid([q_Extract_tcm]![code_csec_loc],1,2)) is derived to create the join with the dbo_clocn data table. The calculated count fields are used to sum counts for the report.

Adjusting the q_Department query

Once the prompted criteria has been entered, the report will run and display.

Sample report for grade entry activity summary counts

The MS Access database that accompanies this sample query can be downloaded here.

Class Lists/Student Listing Database

This database allows an individual to run both class lists and student lists. An example report is shown below;

Example report of class and student lists

This database is campus, semester, class, section and delivery specific. 

The MS Access database that accompanies this sample query can be downloaded here.

New Student Admissions/Registration by Campus (SYS001A)

This database is linked to the student database on the AIS Data Warehouse and produces a report of new admits for the specified semester. The report identifies the type of admission, the new student's current registration status, and the number of late drop credits accumulated.

Start by selecting the QUERIES TAB, highlighting the query qrySYS001A and select DESIGN.

Select the query from the Queries tab and click the Design button

Once you select Design, you will see the query in Design View. Here, you will see the first two fields that you need to enter specific information into. These fields are code_stua_samx_stat and code_stua_samx_camp. They are highlighted below.

Entering the query information for the two database fields

For code_stua_samx_stat, you need to enter one of the following 2 criteria:

For code_stua_samx_camp, you need to enter a campus code, for example:

Once you have entered this information, scroll to the right until you see the fields shown below. These are the next set of fields that require you to enter criteria. These fields are code_stud_lvl and code_stua_samx_type. They are highlighted below.

Adjusting the next two database fields

For code_stud_lvl, you need to enter one of the following 2 criteria:

For code_stua_samx_type, you need to enter:

Once you have entered this information, scroll to the right until you see the fields shown below. These are the next set of fields that require you to enter criteria. These fields are code_stua_samx_yrtm and date_stua_samx_stat. They are highlighted below.

Adjusting the final two database fields

For code_stua_samx_yrtm, you need to enter the admit semester, for example:

For date_stua_samx_stat, you need to enter an admit date that you want to begin searching from, for example:

Now that you have entered all of your criteria, close down the query and return to the query tab. Highlight the query and select the OPEN button to the right. This will run the query with the criteria you entered and produce a table, tblSYS001A. Once Access indicated it has finished running the query, select the REPORTS tab and highlight rptSYS001A. Select the open button to the right, and you will have your report (like seen below).

Sample report for SYSIF001A

This database is campus, admission semester and level specific.

The MS Access database that accompanies this sample query can be downloaded here.

Average GPA Reports (SYSIF123)

This database produces two average GPA reports for students REG/SCH at a specified campus for the current semester. Students with CUM GPA of 0 are excluded. The total number of students is printed at the end of both reports. Report 1: By Major, lists the semester classification, average CUM GPA and the number of students in the semester classification. Report 2: By Campus and Semester Classification, lists the college, semester classification, average CUM GPA within semester classification, average CUM GPA by college and total students in semester classification and college.

Running the Queries

Simply run the queries one at a time in the order that they appear by double clicking, queries #1 through #5.

Running the queries in order

While running these queries, you will be prompted by an input box to enter the campus criteria in the 2 character format (e.g., SL, BK, UP).

Prompt to enter 2 character campus code

Continue to select Yes, when prompted, as you work through queries #1 - #5.

Viewing Reports

After running your last query (#5) select the Reports button under Objects.

Listing of reports generated by previous queries

Simply highlight each report and select the Preview button, at the top of the database window, to view the details. Enter your campus code, when prompted, then print your report from the preview screen.

Report 1 - By Major

Report 2 - By Campus and Semester Classification

The MS Access database that accompanies this sample query can be downloaded here.

Undergraduate Students Graduating at University Park, Formerly at Another Campus (SYSIF147)

This database is linked to the student database on the AIS Data Warehouse and contains two queries. When completed, the final report will provide you with a list of undergraduate students who graduated from University Park during a specific semester that were once enrolled at a campus location you specify.

The first query (#1-qryUGStudentsGraduating) creates a table of all undergraduates approved to graduate from University Park during the semester you specify. This query retrieves the student's name and social security number.

The second query compares the table of the University Park graduates (created from the first query) to the campus location entered and creates a table of students that graduated at University Park but attended the campus location entered. This query retrieves the student's name, social security number, and home address.

The final report, rptUGStudentsGraduatingFormerlyAtOtherLocation, is sorted alphabetically and contains a total count.

Start by selecting the QUERIES TAB, highlighting the query, #1-qryUGStudentsGraduating, and then select DESIGN.

Selecting the first query and hitting the Design button

The first field you see will be code_univ_yr_sem.

Modification of the code_univ_yr_sem database field query

For code_univ_yr_sem, you need to enter the semester the students graduated from University Park, for example:

Now that you have entered your criteria, close the query and return to the query tab. Highlight the query and select the OPEN button to the right. This will run the query with the criteria you entered and produce a table, tblUGStudentsGraduating. Once Access indicates that it has finished running the query, return to the QUERIES tab so that you can run the next query.

Start the next query by selecting #2 - qryUGStudentsGraduatingFormerlyAtOtherLocation and clicking on DESIGN.

Choosing the second query and hitting the Design button

Once you select DESIGN you will see the query in Design View. Scroll to the right until you see the fields shown below. You should now see the one field that requires you to enter criteria. This field is code_camp.

The database table showing the code_camp field query

For code_camp, you need to enter a campus code for the campus of the former campus location, for example:

Now that you have entered all of your criteria, close the query and return to the query tab. Highlight the query again and select the OPEN button to the right. This will run the query with the criteria you entered and produce a table, tblUGStudentsGraduatingFormerlyAtOtherLocation. Once Access indicated it has finished running the query, select REPORTS tab. Highlight rptUGStudentsGraduatingFormerlyAtOtherLocation and select OPEN to view your report.

Sample Data Warehouse report

This database is both semester and campus specific.

The MS Access database that accompanies this sample query can be downloaded here.

[ Back to Top ]