CS105 Access Lab1

Summary:

In this lab you will use an existing access database containing a simplfied college record set. You will write queries and generate reports from those queries. Because you are mostly 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.

• 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)
• You need to generate a report for the advisor of some of the students. Write the (access) report that includes the first and last names of all of the students required by the directions below along with the course names of all of the courses that they are taking. However, the college politics have resulted in the need to change the term 'course name' to 'course title' for this report only. You will need to have the column name changed for this report only. Also make sure to have all of the courses for the same student grouped together so that the advisor can see what that student is taking easily.
  • Use different students depending on your last name
  • If your last name begins with A-D: your report should include all students whose last names begin with A-D
  • If your last name begins with E-G: your report should include all students whose last names begin with E-G
  • If your last name begins with H-O: your report should include all students whose last names begin with H-L
  • If your last name begins with P-S: your report should include all students whose last names begin with N-W
  • Hint: You only need to have existing students show up in the report, so, for example, if there is no student whose last name begins with X, you need not worry about putting such a student in your report.
  • Hint2: look at the data again.
  • Hint3: you probably want to write a query to gather the data and then generate a report from the query.
  • Make sure that you add your name to the report and print it. Use whatever style you think looks best for the report.
• You need one more 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 on Monday, we used the result of one query to generate a second query (the crosstab 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 three pages. If it has more, I'm pretty sure you did something wrong.
• Submit these two reports to me either in my mail box in the computer science department (Hart 215), slip it under my door (Moakley 226) or give it to me during class or my office hours.

• This lab is due by 5pm Tuesaday December 7^th.