Introduction:
We have not created a database from scratch this semester. So this will be our first "from scratch" database in skills 2. In this activity we will create a database where we will enter information about all the skills 2 students this semester and eventually add this data to a larger database I have been keeping about all my skills 2 students over the years. Over time this database will grow to be quite large.
Procedure:
Part 1 Build the Database
- Open access and choose to create a "new" "blank" database
- Name it last first student information.mdb
- Create a table in design view and create the following fields with correct formatting:
Be sure to make the "autonumber" field the primary key.
Part 2 Insert Student Information
You will find all the students data from all Skills 2 classes in the files to copy folder on tech2. You will need to copy and paste this information from these databases into your own in order to get all the student information from this years classes. Copy and paste the database(s) locally . I will give you detailed instructions during class about how to do this.
Be sure the field order in all the databases are in the same order .
Before you do any copying and pasting follow these instructions:
- Open each database
- Check the order of the fields
- Be sure each matches exactly the order of yours
- If not move them so they are.
Failure to do this will cause corrupt data and force you to start over
Directions for copy and pasting data:
- Highlight and copy all the data in a different classes database.
- Open your data base and insert the data at the bottom of the existing data.
- MAKE SURE THAT YOU PUT YOUR POINTER IS IN THE GREY AREA TO THE LEFT OF THE ROW WHEN YOU DO THIS. In the picture below, the white arrow below Samantha Hobbins is where your pointer should be.
Part 3 Sorting and Filtering
**Note- these images will not contain the exact data of your database. They still serve as good examples for you to use if you have trouble remembering some of these skills**
Sorting:
Now that all the data is entered we can start using the database. There are many ways to filter and sort. To sort the names alphabetically, highlight the last name field at the top of the database and hit the A to Z icon. That's it. It sorts the names alpahbetically.
You may sort the data in other ways but this is fast and easy.
Filtering:
Filtering means to get out of the database only the information you want. Once again there are many ways to do this and we will practice different methods, but when the table is open you can search the data quickly and easily. Go to a blank cell at the bottom of any field. Right click and you can type in anything you want to the "filter" line and it will get that information from the database. Below I typed *movies* into the filter in the blank cell under interests..
When I hit enter, this is the result:
Notice that the * attatched to movies caused the filter to return any entry of movies in the database. Also notice that the "filter" icon in the toolbar will be highlighted indicating a filter is on. Left click on the filter icon and it removes the filter and returns the database to show all the records. You may search for specific numbers, you can use > or < or even >= in combination. With these instructions you are now ready to use the information to find all kinds of information about the students in our class.
Part 4 Answering questions using the combined classes database
You should have all the information in one database (over 600 records!). Congratulations!!!
Select this link to download a Word document with the questions. This document will be available in "files to copy" too.
Save the document as: last first stu info questions
Answer the questions below. Any problem that requires a list should be copy/pasted into this Word document
Pay attention to the warning for problems 6 and 7
1. How many girls are there? How many boys?
2. How many boys are 16 or older ?
3. How many students are interested in computers ?
4. List all the girls that are interested in computers .
5. List all the students (names only this time) that share one of your interests . Be clear about your interest of choice.
Warning!
Questions 6 and 7 must have answers recorded in your Word document and Queries/Reports saved in your database too!
6. Click on the queries tab and then click on create query by wizard.
a) Follow the instructions so the query will show:
first name, last name and interests
Recording will take place in part b belowb) Go back to design view.
Sort the last names alphabetically in ascending order.
Copy and paste the first 3 records in the query.
7. Create Queries and Reports for each of the following. Record your results in your Word document and save Queries/Reports in your database.
a) List all the students that are currently sophomores and are interested in skateboarding.
b) List all the students that are 16, listed an interest in "friends" in any way, and are male.
c) From my period 2, 2003-2004 class , list the students that liked to read.
d) List the students that are 16 years old and are interested in cars or driving. Also include grad year
I will be grading the answers to 6 and 7 in both your Word document (copy/pasted answers) and in your database (saved queries and reports)
