Gathering Data from Access Databases Sent from IST
Relationships between tables have to be established. Under Tools, choose Relationships. Add Student Table and Schedule Details and drag "Student ID" from Student Table to Schedule Details. This creates a relationship between the two tables.
Query for fields now that a relationship has been created. SSN, First_Name, Last_Name are good fields to query for.
Make Table Query will turn the results of the query for SSN, First Name and Last Name into a Table.
Delete the Primary Key if you don't need it. This is a column in the newly created table.
Go to "Find"
In the date field type >startingdate<endingdate
Open a new database in Access (e.g. Number of Consultations and Frequency of Visits)
Click on File and then Get External Data. Bring the table that has the SSN, First Name and Last Name into this new database.
Copy data from FilemakerPro database (saved as an Excel file) and paste it into the table in the new database.
Increase in the number of students helped
Increase in the number of consultations
Increase in the number of professors whose students brought coursework to UWC.
Summer, Fall and Spring databases altogether.
Break down the data into the different colleges. How many consultations for COHPA? A&S?
Number of graduate students
Create a Simple Query for SS# in the table that includes the records for the entire year (summer, fall, spring).
Click on Design in that query.
Click on the Sum button (looks like an E and is located on top toolbar).
Create another column by dragging "SSN" from one column to another. There should be two columns for SSN now.
Another row should appear in the Design of the Query.
In one of the columns (doesn't matter which) click on small drop-down menu and turn the column into "Count."
Click on the button underneath "File" (it allows you to view the count).
In the query it will show the number of times that the SS# visited and give a number of records of unique SS#s. (It has grouped by SS# and counted each record associated with that #).
Create a Query in Design View that includes the "Schedules Details" and the "Student" table and searches the "Grad appointment," Social Security, Professor Last Name, Student First and Last Name fields. The two tables must have a relationship established (e.g. "Student ID")
Under criteria type "yes." This tells the query to find those consultation records which have a check mark in the box for "Is this a graduate consultation?"
Make a column to count (see previous instructions) the number of grads.
Make columns for social security number, student first and last names, and professor last name.
Click the red exclamation point in the Toolbar to run the query.
Print this to find out how many students were served, the number of consultations, and determine where the students came from by finding the colleges in which the professors teach.