CS105 Access Lab1
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.
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 7th.