CS105 Access Lab

Summary:

In this lab you will use an existing access database containing a simplified college record set. You will import records, updating existing records, add forms, write queries and generate reports from those queries. Because several of you are management students, and one of the duties of lower level management is to translate between the plain every-day speech of a customer or upper manager and the formal capabilities of the tools you are using (access in this case) I have given you your task in plain english, and then given you hints and some instructions for doing the task in access afterwards.

Directions:

Copy the database file into your own space. (If you are on campus, map \\webhost\jsantore as a drive and copy over AccessLab1.mdb to your space.

Before opening the file, rename it to file name that is your last name followed by Access1.mdb (so for me: SantoreAccess1.mdb)

Look at the data. Take a look at what you are working with. By knowing what you have, you will have an easier time understanding the remaining directions. There are three tables, one for student records, one for course listings, and one for registrations information. In using the data you will notice that there are already some relationships setup for you. You shouldn't need to add any more (if the relationships look a little funny - they are still fine)

Do the next two steps in order to avoid referential integrety errors. The others may be done in any order you find effective.

The file \\webhost\jsantore\More-Students.xls contains many new students. Add this data to the students table. (Note that these new students don't have a county field. Thats ok, the county field is not manditory in the student table.)

The file \\webhost\jsantore\New-Registrations.xls contains several dozen new registrations for these students. Add this data to your database.

Create a form for the Registrations table to allow the clerks in the registrars office to enter new registrations without having to look at the rest of the database. This form should have the StudentID and CoursId fields visible, but not the registration ID since that is not something that they could edit anyway.

The administration wants to keep the students year code in the student table. Possble codes are FRESH, SOPH, JUNIOR, SENIOR. Add a column for this in the student table between the student's Last name and address. Make sure that only the above code can be entered. Make the default code FRESH, but give the existing students the following codes: All of the students should get the code SOPH, except:

Johan Straus:	SENIOR
Janet Style	SENIOR
Blair Keen	FRESH
Timothy Brookes	JUNIOR
Than Vyun	JUNIOR

You need another report: This one should give the students first and last name, the total charge for those credits and the student's address, city, and postal code. No other fields should show up on the report. Alphabetize the list based on the last names to students.

calculate the total charge for the students by charging $425 per credit.
I think that you will find that the easiest way to generate this report is to write more than one query. In class, we have used the result of one query to generate another query. I think you will want to do something similar here. Access does not make a big distinction between tables and queries when deciding on the input to other queries, to see both, choose the 'both' tab in the "Show Table" dialog when creating a new query.
Make sure that your name is on the report and print the report.

Each report should have no more than 5 pages. If it has more, I'm pretty sure you did something wrong.

Submit these two reports (and the word document described below) to me either in my mail box in the computer science department (Hart 215), slip it under my door (Hart 220) or give it to me during class or my office hours.

Data Analysis:

Your supervisor, an administrator at the college, has asked you to work up an analysis of the data to answer some questions asked by the Deans of the college. You are asked to write up a quick summary report in Microsoft word that answers the questions in brief, and then gives the name of the query or report in the database to use in case the supervisor needs to see more information. You need to answer the following questions:

Which courses are least popular (which courses have the lowest enrollment) if there is more than one course with the same low enrollment, list them all.

Which courses are the most popular, list at least the top 3, including any ties. (feel free to cut and paste these name if you like.)

Which courses make the college the most money this semester. Show at least the top 3 money makers and any ties.

According to the post-september 11 international student rules, the college must keep a list of all foriegn exchange students. Please make a report including all such students, ordered by last name, and mention it in the summary document.

Since the registrar's office is busy, you have been asked to help generate some of the class lists. Please generate the class lists for the following courses (using the actual course names, so that the Deans can read the report:

If your first name begins with:

A-L: Give class lists for COMP340, GEOG110, ARCH210, LING101

M-Z: Give the class lists for HIST131, PSYC337, PHIL310, POLY270

This lab is due by 8pm Thursday December 15^th. You are however welcom to submit it early. To submit your database please bring it to class on some sort of media, (usb drive, or cd) or see me during a break or after class to copy it from your network drive to my own. The BSC email system silently and effectively removes all access databases from BSC email with no warning.